Sql-server – Encoding Debug UTF8 & Latin 1

sql serversql-server-2008-r2utf-8

We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2).

For example we have strings like 'WilcoxonÃƒÆ’Ã†â€™'.

We got this data into the db because we take international submissions but management didn't want to use any character set even though we tried for years to make that happen. Now we finally convinced them to implement character set and new data is saved correctly.

What we are trying to do now, is do a search and replace for existing records (around 300 or so) with the incorrect data on them.

We've taken the UTF 8 debug table from here UTF8 Debug and turned it into SQL Table.

This is the code to create the table (I've tried using SQL Fiddle but it's taking ages to create the schema)

CREATE TABLE UTF8Encoding (
    id            int NOT NULL identity,
    [unicode]     nvarchar(250) NOT NULL default '',
    [windows1252] nvarchar(250) NOT NULL default '',
    [expected]    nvarchar(250) NOT NULL default '',
    [actual]      nvarchar(250) NOT NULL default '',
    [utf8bytes]   nvarchar(250) NOT NULL default '',
    PRIMARY KEY (id)
);

INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+20AC','0x80',N'€','â‚¬','%E2 %82 %AC');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('','0x81',N'','','');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+201A','0x82',N'‚','â€š','%E2 %80 %9A');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0192','0x83',N'ƒ','Æ’','%C6 %92');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+201E','0x84',N'„','â€ž','%E2 %80 %9E');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2026','0x85',N'…','â€¦','%E2 %80 %A6');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2020','0x86',N'†','â€','%E2 %80 %A0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2021','0x87',N'‡','â€¡','%E2 %80 %A1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+02C6','0x88',N'ˆ','Ë†','%CB %86');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2030','0x89',N'‰','â€°','%E2 %80 %B0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0160','0x8A',N'Š','Å','%C5 %A0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2039','0x8B',N'‹','â€¹','%E2 %80 %B9');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0152','0x8C',N'Œ','Å’','%C5 %92');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('','0x8D',N'','','');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+017D','0x8E',N'Ž','Å½','%C5 %BD');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('','0x8F',N'','','');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('','0x90',N'','','');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2018','0x91',N'‘','â€˜','%E2 %80 %98');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2019','0x92',N'’','â€™','%E2 %80 %99');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+201C','0x93',N'“','â€œ','%E2 %80 %9C');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+201D','0x94',N'”','â€','%E2 %80 %9D');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2022','0x95',N'•','â€¢','%E2 %80 %A2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2013','0x96',N'–','â€“','%E2 %80 %93');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2014','0x97',N'—','â€”','%E2 %80 %94');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+02DC','0x98',N'˜','Ëœ','%CB %9C');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+2122','0x99',N'™','â„¢','%E2 %84 %A2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0161','0x9A',N'š','Å¡','%C5 %A1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+203A','0x9B',N'›','â€º','%E2 %80 %BA');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0153','0x9C',N'œ','Å“','%C5 %93');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('','0x9D',N'','','');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+017E','0x9E',N'ž','Å¾','%C5 %BE');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+0178','0x9F',N'Ÿ','Å¸','%C5 %B8');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A0','0xA0',N'','Â','%C2 %A0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A1','0xA1',N'¡','Â¡','%C2 %A1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A2','0xA2',N'¢','Â¢','%C2 %A2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A3','0xA3',N'£','Â£','%C2 %A3');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A4','0xA4',N'¤','Â¤','%C2 %A4');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A5','0xA5',N'¥','Â¥','%C2 %A5');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A6','0xA6',N'¦','Â¦','%C2 %A6');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A7','0xA7',N'§','Â§','%C2 %A7');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A8','0xA8',N'¨','Â¨','%C2 %A8');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00A9','0xA9',N'©','Â©','%C2 %A9');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AA','0xAA',N'ª','Âª','%C2 %AA');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AB','0xAB',N'«','Â«','%C2 %AB');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AC','0xAC',N'¬','Â¬','%C2 %AC');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AD','0xAD',N'','Â','%C2 %AD');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AE','0xAE',N'®','Â®','%C2 %AE');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00AF','0xAF',N'¯','Â¯','%C2 %AF');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B0','0xB0',N'°','Â°','%C2 %B0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B1','0xB1',N'±','Â±','%C2 %B1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B2','0xB2',N'²','Â²','%C2 %B2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B3','0xB3',N'³','Â³','%C2 %B3');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B4','0xB4',N'´','Â´','%C2 %B4');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B5','0xB5',N'µ','Âµ','%C2 %B5');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B6','0xB6',N'¶','Â¶','%C2 %B6');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B7','0xB7',N'·','Â·','%C2 %B7');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B8','0xB8',N'¸','Â¸','%C2 %B8');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00B9','0xB9',N'¹','Â¹','%C2 %B9');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BA','0xBA',N'º','Âº','%C2 %BA');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BB','0xBB',N'»','Â»','%C2 %BB');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BC','0xBC',N'¼','Â¼','%C2 %BC');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BD','0xBD',N'½','Â½','%C2 %BD');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BE','0xBE',N'¾','Â¾','%C2 %BE');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00BF','0xBF',N'¿','Â¿','%C2 %BF');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C0','0xC0',N'À','Ã€','%C3 %80');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C1','0xC1',N'Á','Ã','%C3 %81');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C2','0xC2',N'Â','Ã‚','%C3 %82');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C3','0xC3',N'Ã','Ãƒ','%C3 %83');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C4','0xC4',N'Ä','Ã„','%C3 %84');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C5','0xC5',N'Å','Ã…','%C3 %85');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C6','0xC6',N'Æ','Ã†','%C3 %86');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C7','0xC7',N'Ç','Ã‡','%C3 %87');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C8','0xC8',N'È','Ãˆ','%C3 %88');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00C9','0xC9',N'É','Ã‰','%C3 %89');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CA','0xCA',N'','ÃŠ','%C3 %8A');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CB','0xCB',N'Ë','Ã‹','%C3 %8B');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CC','0xCC',N'Ì','ÃŒ','%C3 %8C');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CD','0xCD',N'Í','Ã','%C3 %8D');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CE','0xCE',N'Î','ÃŽ','%C3 %8E');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00CF','0xCF',N'Ï','Ã','%C3 %8F');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D0','0xD0',N'Ð','Ã','%C3 %90');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D1','0xD1',N'Ñ','Ã‘','%C3 %91');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D2','0xD2',N'Ò','Ã’','%C3 %92');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D3','0xD3',N'Ó','Ã“','%C3 %93');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D4','0xD4',N'Ô','Ã”','%C3 %94');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D5','0xD5',N'Õ','Ã•','%C3 %95');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D6','0xD6',N'Ö','Ã–','%C3 %96');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D7','0xD7',N'×','Ã—','%C3 %97');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D8','0xD8',N'Ø','Ã˜','%C3 %98');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00D9','0xD9',N'Ù','Ã™','%C3 %99');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DA','0xDA',N'Ú','Ãš','%C3 %9A');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DB','0xDB',N'Û','Ã›','%C3 %9B');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DC','0xDC',N'Ü','Ãœ','%C3 %9C');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DD','0xDD',N'Ý','Ã','%C3 %9D');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DE','0xDE',N'Þ','Ãž','%C3 %9E');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00DF','0xDF',N'ß','ÃŸ','%C3 %9F');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E0','0xE0',N'à','Ã','%C3 %A0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E1','0xE1',N'á','Ã¡','%C3 %A1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E2','0xE2',N'â','Ã¢','%C3 %A2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E3','0xE3',N'ã','Ã£','%C3 %A3');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E4','0xE4',N'ä','Ã¤','%C3 %A4');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E5','0xE5',N'å','Ã¥','%C3 %A5');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E6','0xE6',N'æ','Ã¦','%C3 %A6');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E7','0xE7',N'ç','Ã§','%C3 %A7');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E8','0xE8',N'è','Ã¨','%C3 %A8');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00E9','0xE9',N'é','Ã©','%C3 %A9');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00EA','0xEA',N'ê','Ãª','%C3 %AA');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00EB','0xEB',N'ë','Ã«','%C3 %AB');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00EC','0xEC',N'ì','Ã¬','%C3 %AC');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00ED','0xED',N'í','Ã','%C3 %AD');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00EE','0xEE',N'î','Ã®','%C3 %AE');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00EF','0xEF',N'ï','Ã¯','%C3 %AF');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F0','0xF0',N'ð','Ã°','%C3 %B0');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F1','0xF1',N'ñ','Ã±','%C3 %B1');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F2','0xF2',N'ò','Ã²','%C3 %B2');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F3','0xF3',N'ó','Ã³','%C3 %B3');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F4','0xF4',N'ô','Ã´','%C3 %B4');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F5','0xF5',N'õ','Ãµ','%C3 %B5');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F6','0xF6',N'ö','Ã¶','%C3 %B6');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F7','0xF7',N'÷','Ã·','%C3 %B7');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F8','0xF8',N'ø','Ã¸','%C3 %B8');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00F9','0xF9',N'ù','Ã¹','%C3 %B9');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FA','0xFA',N'ú','Ãº','%C3 %BA');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FB','0xFB',N'û','Ã»','%C3 %BB');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FC','0xFC',N'ü','Ã¼','%C3 %BC');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FD','0xFD',N'ý','Ã½','%C3 %BD');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FE','0xFE',N'þ','Ã¾','%C3 %BE');
INSERT INTO UTF8Encoding (unicode,windows1252,expected,actual,utf8bytes) VALUES ('U+00FF','0xFF',N'ÿ','Ã¿','%C3 %BF');

Now I'm trying to create a function or stored procedure to do a search and replace to see if it fixes the issue for those 300 or so records. The function I've got so far takes ages for that string mentioned above.

Here's the code I got so far

DECLARE @inputString VARCHAR(MAX)= 'WilcoxonÃƒÆ’Ã†â€™'
DECLARE @count INT = 4
DECLARE @id INT = 1
DECLARE @expected nvarchar(10), @actual nvarchar(10)
DECLARE @codePos INT, @codeEncoded VARCHAR(7), @startIndex INT
    , @resultString varchar(max)
SELECT @count=COUNT(*) FROM UTF8Encoding

SET @resultString = LTRIM(RTRIM(@inputString))

WHILE @id <=@count
BEGIN

    SELECT @expected = expected, @actual = actual
    FROM UTF8Encoding
    WHERE ID = @id

        SELECT @startIndex = PATINDEX('%' + @actual + '%', @resultString)

        While @startIndex > 8 
        BEGIN
            PRINT @resultString + '|'  + @actual + '|' + NCHAR(@expected)
            SET @resultString = REPLACE(@resultString, @actual, NCHAR(@expected))
            SET @startIndex=PATINDEX('%' + @actual + '%', @resultString)
        END
        SET @id=@id + 46
END

PRINT @resultString

GO

So if any of you have an idea on how to solve this to see if we can fix the encoding that would be great.

Thanks

Best Answer

The reason your function takes ages is because you have empty values for actual in UTF8Encoding. The patindex expression returns 1 when you check for an empty actual so you never exit the inner loop. You can fix that by adding and actual <> '' to the query against UTF8Encoding. Next issue is where you use @expected as parameter to nchar(). The parameter should be an integer so if you remove nchar() your code returns something but I don't think it is what you are looking for. WilcoxonÃƒÆ’Ã†â€™ is translated to WilcoxonÁƒƒÁ††™.

Another approach you can try is to use the XML capabilities in SQL Server. XML in SQL Server is UTF-16 but it is able to load UTF-8 encoded strings and that can be used.

Concatenate your string with a UTF-8 xml declaration and use the value() function to fetch the value from the constructed XML.

I guess you eventually want to use this on a table so here is an example that uses a table variable.

declare @T table(InputString varchar(max))

insert into @T values
('Ã¥Ã¤Ã¶Ã…Ã„Ã–'),
('WilcoxonÃƒÆ’Ã†â€™')

select cast('<?xml version="1.0" encoding="UTF-8"?>'+T.InputString as xml).value('text()[1]', 'nvarchar(max)') as Value
from @T as T

Result:

Value
---------------
åäöÅÄÖ
WilcoxonÃƒÆ’

Best Answer

Related Solutions

Sql-server – SQL Server Storage sql_variant

Sql-server – Parent-Child Tree Hierarchical ORDER

Related Question