TSQL code to remove junk characters by the aid of Noise table

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 199
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 


*/

Comments or Responses

Login to post response