How to Replace Multiple Characters in SQL?

Question

This is based on a similar question How to Replace Multiple Characters in Access SQL?

I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause.

I have the following task: Need to perform a match on a column, and to improve the chances of a match stripping multiple un-needed chars using replace() function

DECLARE @es NVarChar(1) SET @es = '' DECLARE @p0 NVarChar(1) SET @p0 = '!' DECLARE @p1 NVarChar(1) SET @p1 = '@' ---etc...  SELECT * FROM t1,t2  WHERE  REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es)       = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)     ---etc  

If there are >19 REPLACE() in that where clause, it doesn't work. So the solution I came up with is to create a sql function called trimChars in this example (excuse them starting at @22

CREATE FUNCTION [trimChars] (    @string varchar(max) )   RETURNS varchar(max)  AS BEGIN  DECLARE @es NVarChar(1) SET @es = '' DECLARE @p22 NVarChar(1) SET @p22 = '^' DECLARE @p23 NVarChar(1) SET @p23 = '&' DECLARE @p24 NVarChar(1) SET @p24 = '*' DECLARE @p25 NVarChar(1) SET @p25 = '(' DECLARE @p26 NVarChar(1) SET @p26 = '_' DECLARE @p27 NVarChar(1) SET @p27 = ')' DECLARE @p28 NVarChar(1) SET @p28 = '`' DECLARE @p29 NVarChar(1) SET @p29 = '~' DECLARE @p30 NVarChar(1) SET @p30 = '{'  DECLARE @p31 NVarChar(1) SET @p31 = '}' DECLARE @p32 NVarChar(1) SET @p32 = ' ' DECLARE @p33 NVarChar(1) SET @p33 = '[' DECLARE @p34 NVarChar(1) SET @p34 = '?' DECLARE @p35 NVarChar(1) SET @p35 = ']' DECLARE @p36 NVarChar(1) SET @p36 = '\' DECLARE @p37 NVarChar(1) SET @p37 = '|' DECLARE @p38 NVarChar(1) SET @p38 = '<' DECLARE @p39 NVarChar(1) SET @p39 = '>' DECLARE @p40 NVarChar(1) SET @p40 = '@' DECLARE @p41 NVarChar(1) SET @p41 = '-'  return   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(        @string, @p22, @es), @p23, @es), @p24, @es), @p25, @es), @p26, @es), @p27, @es), @p28, @es), @p29, @es), @p30, @es), @p31, @es), @p32, @es), @p33, @es), @p34, @es), @p35, @es), @p36, @es), @p37, @es), @p38, @es), @p39, @es), @p40, @es), @p41, @es) END  

This can then be used in addition to the other replace strings

SELECT * FROM t1,t2  WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es)           = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))    

I created a few more functions to do similar replacing like so trimChars(trimMoreChars(

SELECT * FROM t1,t2  WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es)           = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))) 

Can someone give me a better solution to this problem in terms of performance and maybe a cleaner implementation?

Answer 1

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 ALL SELECT '<' UNION ALL SELECT '(' UNION ALL SELECT ')' UNION ALL SELECT '!' UNION ALL SELECT '?' UNION ALL SELECT '@'  declare @testString varchar(100), @newString varchar(100)  set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!' set @newString = @testString  SELECT @newString = Replace(@newString, item, '') FROM @badStrings  select @newString -- returns 'Juliet ro0zs my s0xrzone'