Suppose we have some record mixed with special characters like
! @ # $ % ^ & * ( ) { } < >
Our objective is to remove those special characters. The below is a technique we can do so
DECLARE @NoisyData VARCHAR(MAX)
DECLARE @Noisetbl TABLE(Noise VARCHAR(100),ReplaceChars VARCHAR(10))
SET @NoisyData = 'hello! DNF * > This is % a (: . nice platform to learn :). Thank you so much!'
INSERT INTO @Noisetbl(Noise,ReplaceChars)
SELECT '!',SPACE(1) UNION ALL SELECT '@',SPACE(1) UNION ALL
SELECT '#',SPACE(1) UNION ALL SELECT '$',SPACE(1) UNION ALL
SELECT '%',SPACE(1) UNION ALL SELECT '^',SPACE(1) UNION ALL
SELECT '&',SPACE(1) UNION ALL SELECT '*',SPACE(1) UNION ALL
SELECT '(',SPACE(1) UNION ALL SELECT ')',SPACE(1) UNION ALL
SELECT '{',SPACE(1) UNION ALL SELECT '}',SPACE(1) UNION ALL
SELECT '<',SPACE(1) UNION ALL SELECT '>',SPACE(1) UNION ALL
SELECT ':',SPACE(1)
SELECT @NoisyData = REPLACE(@NoisyData, Noise, ReplaceChars) FROM @Noisetbl
SELECT @NoisyData CleanedRecords
Result /*
CleanedRecords
------------------
hello DNF This is a . nice platform to learn . Thank you so much
*/