How to fix ANSII character in SQL Server table to UTF-8

Question

I have a data import process to import data from csv file into a table in SQL server.

I have noticed that some columns contain some accented characters.

For example I have noticed the following text in the database table

CAFÃ‰

I open a new file in Notepad++, change the encoding to ANSI and save the file with the above text.

Then change encoding to UTF-8

The result was:

CAFÉ

I am not sure what has gone wrong. But is there any way to fix this problem in the database table?

I would like to display the same CAFÉ in the database table instead of CAFÃ‰

Because when this column is displayed on the website even the encoding is UTF-* on web pages it still shows the string as CAFÃ‰ instead of CAFÉ.

I have also checked the collation type of the column :

SQL_Latin1_General_CP1_CI_AS

Thanks,

You should fix it during import process which is the SSIS package. — Wendy, May 16 '17 at 12:10

score 2 · Answer 1 · answered Sep 12 '17 at 18:38

2

I had a similar problem and I solved it by converting the file (which in my case was a sql script) from UTF-8 to ANSI, since SQL Server does not recognize UTF-8 encoding. It worked for me because I only needed ANSI characters.

answered Sep 12 '17 at 18:38

LMF

21
3

score 2 · Answer 2 · answered Aug 10 '21 at 22:05

Based on SQL - UTF-8 to varchar/nvarchar Encoding issue:

Create a custom function as follows:

CREATE FUNCTION dbo.convert_utf8(@utf8 VARBINARY(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @rslt NVARCHAR(MAX);

    SELECT @rslt=
    CAST(
          --'<?xml version="1.0" encoding="UTF-8"?><![CDATA['
        + @utf8
        --']]>'
        
    AS XML).value('.', 'nvarchar(max)');

    RETURN @rslt;
END
GO

Then update the corrupted field as follows:

update [my_table] set my_field = dbo.convert_utf8(cast(my_field as varbinary(MAX)))

I tested it in SQLServer 2019

score 1 · Answer 3 · answered Jun 18 '17 at 21:24

I had the same problem and ended exporting the table using the Integration Tools to MySQL and running the following query:

UPDATE myTable Set thefield= CONVERT(BINARY CONVERT(thefieldUSING latin1) USING utf8);

This was the only way I was able to solve it.

score 1 · Answer 4 · edited Oct 06 '19 at 18:17

I came up with a solution this by creating a mapping table between Expected characters and Actual characters from this webisite http://www.i18nqa.com/debug/utf8-debug.html

Once I have the mapping table then did join to my original table where Like actual characters and replace those characters with the expected ones.

UPDATE rd
SET rd.Name = REPLACE(Name, m.Actual,m.Expected)
FROM RawData rd
INNER JOIN dbo.UtfMapping m ON  rd.Name LIKE '%'+m.Actual+'%' and LEN(m.Actual) = 3;

UPDATE rd
SET rd.Name = REPLACE(Name, m.Actual,m.Expected)
FROM RawData rd
INNER JOIN dbo.UtfMapping m ON  rd.Name LIKE '%'+m.Actual+'%' and LEN(m.Actual) = 2;

UPDATE rd
SET rd.Name = REPLACE(Name, m.Actual,m.Expected)
FROM RawData rd
INNER JOIN dbo.UtfMapping m ON  rd.Name LIKE '%'+m.Actual+'%' and LEN(m.Actual) = 1;

How to fix ANSII character in SQL Server table to UTF-8

4 Answers4