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

user_875708 0
Viewed 12309

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,

utf-8 ansi sql-server
4 Answers

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

user_40300480
Related