SQL - Replacing all "ASCII/special characters" in a string

Question

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.

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:

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringÀÆ
 2           Æ                   AE            ÆStringÆ
 3           Ç                   C             StrÇÀing

Example after:

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringAÆ
 2           Æ                   AE            AEStringAE
 3           Ç                   C             StrCÀing

Preferred Output:

num   SpecialCharacter    MappedCharacter    StringTest
 1           À                   A             StringAAE
 2           Æ                   AE            AEStringAE
 3           Ç                   C             StrCAing

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.

I haven't quite figured out how to do that just yet.

Here are the two SQL code that I have been trying to modify (I only need one to work)

First Method:

              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 <> MappedChar

                END

                CLOSE @mapCursor;
                DEALLOCATE @mapCursor;

Second Method:

            DECLARE @ASCIINum INT = 0

            WHILE (1 = 1) 
            BEGIN  

              SELECT @ASCIINum = ASCIINum
              FROM [intranet].[dbo].[CharMapTestTab]
              WHERE ASCIINum > @ASCIINum 
              ORDER BY ASCIINum

              IF @@ROWCOUNT = 0 BREAK;

                  UPDATE [intranet].[dbo].[CharMapTestTab]
                  SET StringTest = REPLACE(StringTest, SpecialChar, MappedChar)
                  WHERE SpecialChar <> MappedChar


              SELECT TOP 1000 [ASCIINum]
                  ,[SpecialChar]
                  ,[MappedChar]
                  ,[StringTest]
              FROM [intranet].[dbo].[CharMapTestTab]



            END

You need Fetch Next inside your loop as well. As in Fetch first while @@FetchStatus = 0 do something FetchNext end — Tony Hopkinson, Mar 06 '14 at 23:40

t-clausen.dk · Answer 1 · 2014-03-07T13:32:11.510

5

Try this, it works better than looping because there is only 1 update:

-- create test table vc
create table vc(StringTest varchar(20))
insert vc values('StringÀÆ'), ('ÆStringÆ')
go

-- create test table CharacterMapping
create table CharacterMapping(SpecialCharacter char(1), MappedCharacter varchar(2))
insert CharacterMapping values('À', 'A'),('Æ', 'AE'), ('Ç', 'C')
go

--build the varchar for updating
declare @x varchar(max) = 'StringTest'
select @x = 'replace('+@x+', ''' + SpecialCharacter + ''','''+MappedCharacter+''')'  
from CharacterMapping
set @x = 'update vc set StringTest=' + @x +' from vc'

exec (@x)

select * from vc

Result:

StringAAE
AEStringAE

edited Mar 07 '14 at 13:32

answered Mar 07 '14 at 10:01

t-clausen.dk

43,517
12
59
92

Wow - very cool! I didn't know you could recursively build a REPLACE string like that in a simple SELECT. I wonder if there are any restrictions on how many REPLACE commands can be strung together? BTW, if you use temp tables in your example it is easier for people to run the code and not mess up their dev. databases with tables... ;-) – David Coster May 30 '14 at 06:26

score 1 · Answer 2 · answered Mar 07 '14 at 00:29

I would make a separate mapping table which contains the bad character and its corresponding good character, one set per row. Then loop over that table and do a replace for each character set.

DECLARE @map TABLE (
    id INT,
    badChar CHAR,
    goodChar CHAR
)

DECLARE @strings TABLE (
    searchString VARCHAR(50)
)

INSERT INTO @map 
VALUES 
(1, 'y', 'a'),
(2, 'z', 'b')

DECLARE @curRow INT, @totalRows INT
SET @curRow = 1
SELECT @totalRows = COUNT(*) FROM @map

INSERT INTO @strings
VALUES
('zcccyccz'),
('cccyccz')

WHILE @curRow <= @totalRows
BEGIN
    UPDATE @strings 
    SET searchString = REPLACE(searchString, badChar, goodChar) 
    FROM @map 
    WHERE id = @curRow

    SET @curRow = @curRow + 1
END

SELECT * FROM @strings

--Output
--bcccaccb
--cccaccb

paparazzo · Answer 3 · 2014-03-07T14:15:21.897

0

update table  
set column = REPLACE(column,'À','A') 
where column like ('%À%') 
update table  
set column = REPLACE(column,'Æ','AE') 
where column like ('%Æ%')

I will leave the 3rd to you

Or this might be more efficient

update table  
set column = REPLACE(REPLACE(column,'À','A'),'Æ','AE')
where column like ('%À%') 
   or column like ('%Æ%')

If you really want to process a list of mapped characters then this is not a proper answer

edited Mar 07 '14 at 14:15

answered Mar 07 '14 at 00:17

paparazzo

44,497
23
105
176

QA already knows how to replace a character. The question is an example there could be a different set of characters to be replaced. This could possible change over time. So your solution is not good. – t-clausen.dk Mar 07 '14 at 09:11
@t-clausen.dk OK this is an example of how to without a cursor. And I like you answwer +1. – paparazzo Mar 07 '14 at 12:42

score 0 · Answer 4 · answered Mar 07 '14 at 13:56

It would be helpful to know how many rows are in your table and how many you estimate to have "special characters". Also, are there only 3 special characters? if you have 40 or less special characters, it may look ridiculous, but I'd just nest as many REPLACE() calls as you have special characters, like:

UPDATE YourTable SET YourColumn = REPLACE(
                                  REPLACE(
                                  REPLACE(YourColumn,'Ç','C')
                                  ,'Æ','AE')
                              ,'À','A')

if most rows have special characters, I'd skip any WHERE. if only a few rows have special characters, I'd use a CTE to identify them:

;WITH AllSpecialRows AS
(
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%À%'
UNION 
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%Æ%'
UNION 
SELECT PrimaryKey FROM YourTable WHERE YourColumn LIKE '%Ç%'
)
UPDATE y
    SET YourColumn = REPLACE(
                     REPLACE(
                     REPLACE(YourColumn,'Ç','C')
                     ,'Æ','AE')
                     ,'À','A')
    FROM YourTable                  y
        INNER JOIN AllSpecialRows   s ON y.PrimaryKey =s.PrimaryKey

I am probably going to be working with close to 100 characters so I think it would be easier to create a separate table with the "special characters" and "replacement characters" and I will remove those columns from the table that contains the string. I will work on figuring out how to replace the characters based on that scenario. — BrettKB, Mar 08 '14 at 18:05
even with 100, I'd just hard code them in a nested `REPLACE()`. If this is a one time "fix" of the table, just get it done, you don't need anything fancy or complex. — KM., Mar 10 '14 at 13:28

score 0 · Answer 5 · answered Aug 06 '14 at 11:38

@t-clausen.dk answer with Table variables and temp tables, just to avoid people mess up their dev databases with additional tables.

TABLE Variables:

-- Create test table variable @CharacterMapping
DECLARE @CharacterMapping TABLE (SpecialCharacter char(1), MappedCharacter varchar(2))
INSERT @CharacterMapping VALUES('À', 'A'), ('Æ', 'AE'), ('Ç', 'C')

--Build the varchar for updating
DECLARE @x varchar(max) = 'StringTest'
SELECT @x = 'replace('+@x+', ''' + SpecialCharacter + ''',''' + MappedCharacter + ''')'  
FROM @CharacterMapping
SET @x = 'DECLARE @vc TABLE(StringTest varchar(20));'
        + ' insert @vc values(''StringÀÆ''), (''ÆStringÆ'');'
        + 'update @vc set StringTest=' + @x +' from @vc;' 
        + 'SELECT * FROM @vc;'

Exec (@x)
GO

With Temp table:

-- Create test temp table #vc
CREATE TABLE #vc(StringTest varchar(20))
INSERT #vc VALUES('StringÀÆ'), ('ÆStringÆ')

-- Create test table CharacterMapping
DECLARE @CharacterMapping TABLE (SpecialCharacter char(1), MappedCharacter varchar(2))
INSERT @CharacterMapping VALUES('À', 'A'), ('Æ', 'AE'), ('Ç', 'C')

--Build the varchar for updating
DECLARE @x varchar(max) = 'StringTest'
SELECT @x = 'replace('+@x+', ''' + SpecialCharacter + ''',''' + MappedCharacter + ''')'  
FROM @CharacterMapping
SET @x = 'update #vc set StringTest=' + @x +' from #vc'

-- Execute
EXEC (@x)

-- Select the results 
SELECT * FROM #vc;

-- Drop temp table
DROP TABLE #vc;

GO

SQL - Replacing all "ASCII/special characters" in a string

5 Answers5

Linked