CODEWITHSUNDEEP
sqlsql-server
nikhil pinto
nikhil pinto

Reputation: 331

Selecting chinese characters in SQL server 2008

The following query returns desired result as "John 朱亚丽": 

DECLARE @FIRST_NAME nvarchar(30),@LAST_NAME nvarchar(30)
SET @FIRST_NAME=N'John'
SET @LAST_NAME =N'朱亚丽'
SELECT @FIRST_NAME + ' ' + @LAST_NAME

However when i change this to the below (ive removed the N before the quotes), i get the result as John ???: 

DECLARE @FIRST_NAME nvarchar(30),@LAST_NAME nvarchar(30)
SET @FIRST_NAME='John'
SET @LAST_NAME ='朱亚丽'
SELECT @FIRST_NAME + ' ' + @LAST_NAME

Now i know about the unicode characters and why we need the N''. But i face a problem when i am selecting some Chinese characters from my database column. The columns are defined as NVARCHAR and store the Chinese characters as well, but when i run a simple select on the table the characters show up as question marks.

How do i solve the issue? Is there any function or character i append before the column (or the variables in above example)?

Version of SQL SERVER is 2008 R2.

Upvotes: 1

Views: 11876

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Probably the data was lost when it was inserted (because it didn't have the N prefix then). For example:

DECLARE @x TABLE(ID INT IDENTITY(1,1), n NVARCHAR(32));

INSERT @x SELECT N'朱亚丽';
INSERT @x SELECT '朱亚丽';

SELECT ID, n FROM @x;

Results:

1    朱亚丽
2    ???

Based on your description, I seriously doubt this is happening on the SELECT.

And if this is the case, there is no fix for this - whatever data you meant to insert into the table is long gone with no chance of recovery (unless it came from a file or other source that you can re-import).

Upvotes: 4

Related Questions