Convert all data in SQL Server to unicode

Question

My database table string columns are nvarchar.

But when I insert my data using application, I forgot to add the N' prefix. So the display is not UTF-8 on web. This causes lot of problems.

How do I use some T-SQL command to replace all existing tables and all string columns type and data to UTF-8?

I think a query cannot canvert your data to unicode now. Unicode is 16 bit, while ASCII is 8 bit, if you forgot N prefix, only 8 bit data got inserted to database, and it is not possible for a query to recover the lost 8 bit of data. You'll have to reinsert the data using the N prefix. Correct me if I am wrong? — Amit Mittal, May 10 '13 at 07:21
If it helps, give this a try: [Convert varchar to nvarchar](http://stackoverflow.com/questions/28168055/convert-text-value-in-sql-server-from-utf8-to-iso-8859-1) — F0r3v3r-A-N00b, Nov 09 '15 at 02:15

score 2 · Accepted Answer · answered May 10 '13 at 13:45

2

First, please note that SQL Server doesn't support UTF-8, it supports UTF-16. So it's possible that you still have an encoding problem in your application code (you didn't show any sample data or code, so it's hard to say exactly what's going on).

Having said that, you can't simply UPDATE the data to change it to Unicode:

declare @t table (c nchar(1))

insert into @t select '말'
insert into @t select N'말'

select c, ascii(c), unicode(c) from @t

update @t set c = cast(c as nchar(1))

select c, ascii(c), unicode(c) from @t

As you can see, the character 말 is stored as ASCII 63 if you don't use the N prefix, and even if you convert it to Unicode explicitly, SQL Server has no way to magically know that you really meant it to be Unicode code point 47568. So the only thing you can do is go back and re-INSERT all your data correctly.

answered May 10 '13 at 13:45

Pondlife

15,992
6
37
51

my application use Peta Poco and it is only like PetaPoco_Obj.insert(data). No SQL statement on my side. It is done by the ORM itself. – Hami May 16 '13 at 03:34
yes, you are right. I tried to re-**UPDATE** and it doesn't work. but I cannot re-**INSERT** the data as I need the original IDs and the amount of data is quite substantial. – Hami May 16 '13 at 03:47
1

Unfortunately, if your application has stored the wrong values in the database then SQL Server has no way to know what the correct values should be. – Pondlife May 16 '13 at 13:24

score 1 · Answer 2 · answered May 10 '13 at 06:43

Try this one -

Query:

SELECT 
      o.table_name
    , column_name = c.name
    , transform_to = UPPER(t.name) + ' -> ' + CASE WHEN t.name = 'char' THEN 'NCHAR' ELSE 'NVARCHAR' END
    , 'ALTER TABLE ' + o.table_name + 
        ' ALTER COLUMN [' + c.name + '] ' +
        CASE WHEN t.name = 'char' 
        THEN 'NCHAR('
        ELSE 'NVARCHAR('
        END + CASE WHEN c.max_length != -1 THEN CAST(c.max_length * 2 AS NVARCHAR(10)) ELSE 'MAX' END + ')'
FROM sys.columns c WITH (NOWAIT)
JOIN (
    SELECT 
          table_name = '[' + s.name + '].[' + o.name + ']'
        , o.[object_id]
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
) o ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text')
ORDER BY 
      o.table_name
    , c.column_id

Results:

table_name                      column_name      transform_to          
------------------------------- ---------------- --------------------- ---------------------------------------------------------------------------------------
[dbo].[Employee]                TabelNumber      VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[Employee] ALTER COLUMN [TabelNumber] NVARCHAR(12)
[dbo].[EmployeeAssigned]        EmployeeTypeCD   VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[EmployeeAssigned] ALTER COLUMN [EmployeeTypeCD] NVARCHAR(30)
[dbo].[EmployeeType]            EmployeeTypeCD   VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[EmployeeType] ALTER COLUMN [EmployeeTypeCD] NVARCHAR(30)
[dbo].[PaymentType]             PaymentCode      CHAR -> NCHAR         ALTER TABLE [dbo].[PaymentType] ALTER COLUMN [PaymentCode] NCHAR(4)
[dbo].[ScheduleDetail]          AbsenceCode      VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[ScheduleDetail] ALTER COLUMN [AbsenceCode] NVARCHAR(50)
[dbo].[ScheduleTemplateDetail]  AbsenceCode      VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[ScheduleTemplateDetail] ALTER COLUMN [AbsenceCode] NVARCHAR(50)
[dbo].[Supplement]              WorkFactorCD     VARCHAR -> NVARCHAR   ALTER TABLE [dbo].[Supplement] ALTER COLUMN [WorkFactorCD] NVARCHAR(50)

This script detect char, varchar, text columns and generate changes for problem tables. — Devart, May 10 '13 at 06:45
I think his columns are already nvarchar, the problem is that he forgot the N prefix while insertion of data and now he want to convert the data to unicode format. — Amit Mittal, May 10 '13 at 07:20
@Amit Mittal, Partially agree with you, but question: "to replace all existing tables and all string columns data to UTF-8" — Devart, May 10 '13 at 07:31
this SQL script helps. But like Amit said, I still need the N thingy to help the data to unicode format. — Hami, May 16 '13 at 03:31

Convert all data in SQL Server to unicode

2 Answers2