title: How to Replace Multiple Characters in SQL?
body: <p>This is based on a similar question <a href="https://stackoverflow.com/questions/748674/how-to-replace-multiple-characters-in-access-sql">How to Replace Multiple Characters in Access SQL?</a> </p>
  
  <p>I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause.</p>
  
  <p>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</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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 <strong>trimChars</strong> in this example (excuse them starting at @22</p>
  
  <pre><code>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 = '&amp;'
  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 = '&lt;'
  DECLARE @p39 NVarChar(1) SET @p39 = '&gt;'
  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 
  </code></pre>
  
  <p>This can then be used in addition to the other replace strings</p>
  
  <pre><code>SELECT *
  FROM t1,t2 
  WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
           = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))   
  </code></pre>
  
  <p>I created a few more functions to do similar replacing like so <strong>trimChars(trimMoreChars(</strong></p>
  
  <pre><code>SELECT *
  FROM t1,t2 
  WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
           = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)))
  </code></pre>
  
  <p>Can someone give me a better solution to this problem in terms of performance and maybe a cleaner implementation?</p>