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,' ',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
*/