title: SQL - Replacing all "ASCII/special characters" in a string
body: <p>Edit: I have about 80 characters that are causing problems in my application so I don't want to hard code a REPLACE for every single character. I think it would be easier to create a separate table with two columns,"special characters" and "replacement characters", and I will remove those columns from the original table which contains the column "StringTest". My goal will be figuring out how to use the characters table to replace characters in the string table.</p>
  
  <p>I am trying to replace all "special characters" (ie À, Æ, Ç) with "MappedCharacters" (A, AE, C) in SQL Server. I have tried two different techniques, one using a cursor, one without a cursor, to search through a string and replace all special characters with mapped characters. Each of my methods only replaces characters they are in the same row as the string.
  Example before:</p>
  
  <pre><code>num   SpecialCharacter    MappedCharacter    StringTest
   1           À                   A             StringÀÆ
   2           Æ                   AE            ÆStringÆ
   3           Ç                   C             StrÇÀing
  </code></pre>
  
  <p>Example after:</p>
  
  <pre><code>num   SpecialCharacter    MappedCharacter    StringTest
   1           À                   A             StringAÆ
   2           Æ                   AE            AEStringAE
   3           Ç                   C             StrCÀing
  </code></pre>
  
  <p>Preferred Output:</p>
  
  <pre><code>num   SpecialCharacter    MappedCharacter    StringTest
   1           À                   A             StringAAE
   2           Æ                   AE            AEStringAE
   3           Ç                   C             StrCAing
  </code></pre>
  
  <p>So you can see that I want to replace all "special characters" in StringTest but only characters that are in the same row are getting replaced.</p>
  
  <p>I haven't quite figured out how to do that just yet.</p>
  
  <p>Here are the two SQL code that I have been trying to modify (I only need one to work)</p>
  
  <p>First Method:</p>
  
  <pre><code>              DECLARE @cASCIINum INT;
                DECLARE @cSpecialChar VARCHAR(50);
                DECLARE @cMappedChar VARCHAR(50);
                DECLARE @cStringTest VARCHAR(50);
  
                DECLARE @mapCursor as CURSOR;
  
                SET @mapCursor = CURSOR FOR
                SELECT [ASCIINum]
                      ,[SpecialChar]
                      ,[MappedChar]
                      ,[StringTest]
                FROM [intranet].[dbo].[CharMapTestTab]; 
  
                OPEN @mapCursor;
                FETCH NEXT FROM @mapCursor INTO @cASCIINum,
                                                @cSpecialChar,
                                                @cMappedChar,
                                                @cStringTest;
  
                  WHILE @@FETCH_STATUS = 0
                  BEGIN
  
                    UPDATE [intranet].[dbo].[CharMapTestTab]
                    SET StringTest = REPLACE(StringTest, SpecialChar, MappedChar)
                    WHERE SpecialChar &lt;&gt; MappedChar
  
                  END
  
                  CLOSE @mapCursor;
                  DEALLOCATE @mapCursor;
  </code></pre>
  
  <p>Second Method:</p>
  
  <pre><code>            DECLARE @ASCIINum INT = 0
  
              WHILE (1 = 1) 
              BEGIN  
  
                SELECT @ASCIINum = ASCIINum
                FROM [intranet].[dbo].[CharMapTestTab]
                WHERE ASCIINum &gt; @ASCIINum 
                ORDER BY ASCIINum
  
                IF @@ROWCOUNT = 0 BREAK;
  
                    UPDATE [intranet].[dbo].[CharMapTestTab]
                    SET StringTest = REPLACE(StringTest, SpecialChar, MappedChar)
                    WHERE SpecialChar &lt;&gt; MappedChar
  
  
                SELECT TOP 1000 [ASCIINum]
                    ,[SpecialChar]
                    ,[MappedChar]
                    ,[StringTest]
                FROM [intranet].[dbo].[CharMapTestTab]
  
  
  
              END
  </code></pre>