I have a database imported that contains Arabic characters but are displayed as question marks. Through some searching I found that the column data types should be nvarchar to support unicode (they were varchar). I changed one of the columns that contains those characters to nvarchar but the data is still displayed as "??". How can I change the existing values to become unicode and display correctly?
2 Answers
You cannot just simply change the datatype to nvarchar - that won't bring back the data since it's already been "destroyed" by having been converted to a non-Unicode format.
You need to use nvarchar and then you need to insert (or update) the data in such a way that doesn't convert it back to ANSI codes.
If you use T-SQL to insert that Unicode code, make sure to use the N'...' prefix:
INSERT INTO dbo.YourTable(NvarcharCol)
VALUES (N'nvarchar-value')
From a front-end language like C# or PHP or Ruby, make sure to use Unicode strings - .NET (C# and VB.NET) does that automatically. When using queries with parameters, make sure to specify Unicode string types for those relevant parameters.
2 Comments
AuthenticReplica
Thanks for that. I don't really have that option but I have a backup of the database before I changed the field to unicode. Could I do anything with that to convert the characters to unicode?
marc_s
@TarekMerachli: unfortunately, no - once you've inserted the Unicode characters into the 1-byte-per-character
varchar, the information these characters had is gone. No way to just magically make it reappear.You need different collation.
Read more here: https://msdn.microsoft.com/en-us/library/ms143508.aspx
answered Feb 22, 2015 at 7:57
Evaldas Buinauskas
14.2k11 gold badges63 silver badges119 bronze badges
1 Comment
AuthenticReplica
I also changed the database collation to Arabic_CI_AS but still not changing the displayed characters