Using Unicode characters in SQL Server
Posted by danielmeyer on February 19, 2010
In troubleshooting an issue this week involving the storage of Japanese characters to the database, I found out some things.
When I ran a query on the database using MS SQL Server (2005) Management Studio, the row that should have looked like this
instead looked like question marks:
Is it just my glasses?
My reaction in a situation like this is: “Is it really foggy out, or is it just my glasses?” Either the data was being stored incorrectly in the database or I was simply having trouble viewing it.
Here is a dump of the things I learned:
- The question marks indicate that the data was really not stored correctly (data loss).
- If instead, your query results appear as square boxes (▯▯▯▯▯▯), your PC just does not have the language pack installed to view the characters. On Windows XP, go to
Control Panel -> Regional and Language Optionsand check the
Install files for East Asian languagescheckbox.
- One possible cause of your data turning to question marks is if you insert a literal unicode string but do not use the N prefix (e.g., N‘myunicodestring’). Setting it as just ‘myunicodestring’ can cause non-Latin characters to go to question marks.
- Another cause is if your column is not of a unicode type. For instance, on SQL Server, you would want to use one of the n- character column types such as