TSQL code to remove junk characters by the aid of Number Table

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 241
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)
SET @NoisyData = 'hello! DNF * > This is % a (: . nice platform to learn :). Thank you so much!'

;WITH numberCTE AS
(
SELECT 1 AS Rn
UNION ALL
SELECT Rn+1 FROM numberCTE WHERE Rn<LEN(@NoisyData)
)
SELECT REPLACE(FilteredData,'&#x20;',SPACE(1)) CleanedRecords
FROM
(SELECT SUBSTRING(@NoisyData,Rn,1)
FROM numberCTE
WHERE SUBSTRING(@NoisyData,Rn,1) NOT IN('!','*','>','<','%','(',')',':','!','&','@','#','$')

FOR XML PATH(''))X(FilteredData)


Result
/*
CleanedRecords
------------------
hello  DNF     This is   a    . nice platform to learn   . Thank you so much 


*/

Comments or Responses

Login to post response