Remove bad characters from a string

This simple CTE can be used to remove bad characters in string. It will take string and allowed characters and returns neat string. Change the @AllowedPattern as you wish and check the output string. For example if you have a string abc@#$()123 and you want a string abc123, Set allowedPattern to '0-9a-z. With little tweak this can be converted to a function.





DECLARE @String VARCHAR(8000),
@AllowedPattern VARCHAR(100),
@Holder VARCHAR(8000)
SET @String = '!@#$% 123 ??abc##rd,,,'
SET @AllowedPattern = '0-9a-z'
SET @Holder = ''

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
          FROM t4 x, t4 y)
SELECT @Holder = @Holder + SUBSTRING(@String,num,1)
FROM tally
WHERE num <= LEN(@String)
AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1)) = 1

SELECT @Holder