Using Unicode characters in SQL Server

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.

Answers

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 Options and check the Install files for East Asian languages checkbox.
  • 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 nvarchar(x).
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s