title: SQL Function to replace multiple characters in string
body: <p>I am writing a SQL Server 2008 user-defined function to replace a string's characters with another character. I think the code below should work, but it doesn't. It doesn't seem to be updating the variable for <code>@input</code> correctly.</p>
  
  <p>Any idea how I can fix this?</p>
  
  <pre><code> DECLARE @Input AS VarChar(1000)
   DECLARE @i AS TinyInt
   DECLARE @Substring AS VarChar(1000)
   DECLARE @Prestring  AS VarChar(1000)
   DECLARE @Poststring  AS VarChar(1000)
  
   Select @Input='ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',
          @i = 1
  
   WHILE @i &lt;= LEN(@Input) 
   BEGIN
      SELECT @Prestring = SUBSTRING(@Input,-1,@i)
      SELECT @Poststring = SUBSTRING(@Input,@i+1,LEN(@Input))    
  
      SELECT @Substring =  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
  (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
  (SUBSTRING(@Input,@i,1), 'A', 'N'),'B', 'O'), 'C', 'P'), 'D', 'Q'), 'E', 'R'), 'F', 'S'), 'G', 'T'), 'H', 'U'), 'I', 'V'), 'J', 'W'), 'K', 'X'), 'L', 'Y'), 'M', 'Z'), 'N', 'A'), '0', 'B'), 'P', 'C')
  , 'Q', 'D'),'R', 'E'),'S', 'E'),'T', 'E'),'U', 'F'),'V', 'G'),'W', 'H'),'X', 'I'),'Y', 'J'), '1', '9'),'2','8'),'3','7'),'4','6'),'5','5'),'6','4'),'7','3'),'8','2'),'9','1'),' ','')
  
      SELECT @Input = @Prestring + @Substring + @Poststring
      SELECT @i = @i + 1
  
      PRINT 'Prestring= ' + @Prestring PRINT 'SUBSTRING= ' + @Substring PRINT 'PostString= '     + @Poststring PRINT 'Total String: ' + @Prestring + ' + ' + @Substring + ' + ' + @Poststringenter code here
  
      PRINT 'END'
  
   END
  
  Output:
  Prestring= 
  SUBSTRING= A
  PostString= BCDEFGHIJKLMNOPQRSTUVWXYZ123456789
  Total String:  + A + BCDEFGHIJKLMNOPQRSTUVWXYZ123456789
  1
  END
  
  Prestring= A
  SUBSTRING= O
  PostString= CDEFGHIJKLMNOPQRSTUVWXYZ123456789
  Total String: A + O + CDEFGHIJKLMNOPQRSTUVWXYZ123456789
  2
  END
  
  Prestring= AO
  SUBSTRING= C
  PostString= DEFGHIJKLMNOPQRSTUVWXYZ123456789
  Total String: AO + C + DEFGHIJKLMNOPQRSTUVWXYZ123456789
  3
  END
  
  Prestring= AOC
  SUBSTRING= D
  PostString= EFGHIJKLMNOPQRSTUVWXYZ123456789
  Total String: AOC + D + EFGHIJKLMNOPQRSTUVWXYZ123456789
  4
  END
  
  ......
  </code></pre>