How to Replace Multiple Characters in SQL?

One useful trick in SQL is the ability use @var = function(...) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects:

declare @badStrings table (item varchar(50))INSERT INTO @badStrings(item)SELECT '>' UNION ALLSELECT '<' UNION ALLSELECT '(' UNION ALLSELECT ')' UNION ALLSELECT '!' UNION ALLSELECT '?' UNION ALLSELECT '@'declare @testString varchar(100), @newString varchar(100)set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'set @newString = @testStringSELECT @newString = Replace(@newString, item, '') FROM @badStringsselect @newString -- returns 'Juliet ro0zs my s0xrzone'

I would seriously consider making a CLR UDF instead and using regular expressions (both the string and the pattern can be passed in as parameters) to do a complete search and replace for a range of characters. It should easily outperform this SQL UDF.

I really like @Juliett's solution! I would just use a CTE to get all the invalid characters:

DECLARE @badStrings VARCHAR(100)DECLARE @teststring VARCHAR(100)SET @badStrings = '><()!?@'SET @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!';WITH CTE AS(  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]  UNION ALL  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1   FROM CTE   WHERE [Counter] < LEN(@badStrings))SELECT @teststring = REPLACE(@teststring, CTE.[String], '') FROM CTESELECT @teststring

Juliet ro0zs my s0xrzone