Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?

Question

I'm trying to store Japanese characters in nvarchar fields in my SQL Server 2000 database.

When I run an update statement like:

update blah 
set address = N'スタンダードチャ'
where key_ID = 1

from SQL Server Management Studio, then run a select statement I see only question marks returned to the results window. I'm seeing the same question marks in the webpage which looks at the database.

It seems this is an issue with storing the proper data right? Can anyone tell me what I need to do differently?

score 109 · Answer 1 · edited May 23 '17 at 11:54

109

This cannot be a correct answer given your example, but the most common reason I've seen is that string literals do not need a unicode N prefix.

So, instead of

set address = N'スタンダードチャ'

one would try to write to a nvarchar field without the unicode prefix

set address = 'スタンダードチャ'

See also: N prefix before string in Transact-SQL query

edited May 23 '17 at 11:54

Community

1
1

answered Feb 04 '10 at 15:10

mika

6,812
4
35
38

6

You my friend are a frickin genius! – Chin Aug 12 '10 at 06:01
Thanks for this tip. Useful over three years later. – Rob Lyndon Jul 18 '13 at 10:18
Even helpful 5 years later. Thank you, I was missing this, too. – Deathspike Jan 23 '15 at 13:23
2

in my case there was text instead of ntext – vlukham Jan 31 '16 at 13:03
4

I was adding Romanian text and I had to prefix the string with an the n `N''`. So it was the reverse. The first way I tried was without the N and that did not work. Thank you. – BamBam22 May 21 '18 at 22:39
Well its still helping after 8 years! Thanx ya – Tk1993 Jun 05 '18 at 06:41
1

10 years! A good question for microsoft would be why in the world doesn't SSMS produce an error rather than happily and silently converting the string into ?'s – Marc Bernier Mar 09 '20 at 20:40

Deepashri · Answer 2 · 2014-04-09T11:15:09.803

I was facing this same issue when using Indian languages characters while storing in DB nvarchar fields. Then i went through this microsoft article -

http://support.microsoft.com/kb/239530

I followed this and my unicode issue got resolved.In this article they say - You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server

SQL Server Unicode Support

SQL Server Unicode data types support UCS-2 encoding. Unicode data types store character data using two bytes for each character rather than one byte. There are 65,536 different bit patterns in two bytes, so Unicode can use one standard set of bit patterns to encode each character in all languages, including languages such as Chinese that have large numbers of characters.

In SQL Server, data types that support Unicode data are:

nchar
nvarchar
nvarchar(max) – new in SQL Server 2005
ntext

Use of nchar, nvarchar, nvarchar(max), and ntext is the same as char, varchar, varchar(max), and text, respectively, except:

- Unicode supports a wider range of characters.
- More space is needed to store Unicode characters.
- The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000     characters like char and varchar.
- Unicode constants are specified with a leading N, for example, N'A Unicode string'

APPLIES TO

Microsoft SQL Server 7.0 Standard Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Workgroup Edition

Paresh · Answer 3 · 2017-08-23T07:02:56.600

7

The code is absolutely fine. You can insert an unicode string with a prefix N into a field declared as NVARCHAR. So can you check if Address is a NVARCHAR column. Tested the below code in SQL Server 2008R2 and it worked.

update blah 
set address = N'スタンダードチャ'
where key_ID = 1

enter image description here

edited Aug 23 '17 at 07:02

answered Aug 21 '17 at 13:33

Paresh

109
1
2

score 5 · Answer 4 · answered Nov 03 '15 at 10:35

you need to write N before the string value. e.g.INSERT INTO LabelManagement (KeyValue) VALUES (N'変更命令'); Here I am storing value in japanese language and i have added N before the string character. I am using Sql server 2014. Hope you find the solution. Enjoy.

cjk · Accepted Answer · 2015-08-11T13:49:52.593

3

You need to check the globalisation settings of all the code that deals with this data, from your database, data access and presentation layers. This includes SSMS.

(You also need to work out which version you are using, 2003 doesn't exist...)

edited Aug 11 '15 at 13:49

answered Apr 17 '09 at 16:06

cjk

45,739
9
81
112

How can I see how my current collation is set? Sorry, it's SQL Server 2000, I corrected that. – Stimy Apr 17 '09 at 16:11
If you are using SQL Management Studio (which is a 2005 tool) you can right click on the database in Object Explorer, select Properties, and it is at the bottom in the Maintenance seciton on the General page. – cjk Apr 17 '09 at 16:13
Ok, and if it is set to SQL_Latin1_General_CP1_CI_AS that means I can't store Japanese characters? I think it's time to visit my DBA ;) – Stimy Apr 17 '09 at 16:17
Yeah, AFAIK the Latin charset doesn't include Japanese characters. – cjk Apr 17 '09 at 17:16
11

I don't understand why this answer has (any) votes... The database (or object) collation has nothing to do with what you can and can't store in the DB! It's used for indexing, sorting and general string comparison. That said, the "You need to check the globalisation settings of all the code that deals with this data, from your database, data access and presentation layers" portion is certainly right, and the problem is probably somewhere in there. – Tao Sep 20 '11 at 13:27
1

Bit late to the party here but I ran into this exact same issue today, and it was easily fixed according to this answer by just changing the Collation from SQL_Latin1_General_CP1_CI_AS to Japanese_CI_AS. So that's probably why the answer has votes, hehe :) – CptSupermrkt Aug 01 '12 at 07:51
1

@CptSupermrkt my database collation is SQL_Latin1_General_CP1_CI_AS and I can store and retrieve the string in this question just fine. Tao is correct in that the collation has nothing to do with the correct encoding of Unicode data at rest. – cdonner Aug 10 '15 at 13:26
@Tao and @cdonner - The collation very much affects what you can store if the backing field is `varchar`. For `nvarchar` it is true what @Tao wrote. See for instance [here](https://technet.microsoft.com/en-us/library/aa174903(v=sql.80).aspx). The link is old but what is says is still true: `The code page used to store non-Unicode character data.` – user1429080 Oct 06 '15 at 07:37
@user1429080: Makes sense, but the question explicitly states "in nvarchar fields". Btw, my comment no longer applies, the answer has been changed to no longer mention the collation. Taking into account your and CptSupermrkt's comments maybe the question could/should be changed to refer to once again collation, but explain that while this cannot apply in OP's case, for others it might (depending on the data type of the column in DB). – Tao Oct 06 '15 at 10:48

score 1 · Answer 6 · edited Dec 19 '12 at 15:37

1

I can almost gurantee that the data type is not unicode. If you want to learn more you can check Wikipedia for information on Unicode, ASCII, and ANSI. Unicode can store more unique characters, but takes more space to store, transfer, and process. Also some programs and other things don't support unicode. The unicode data types for MS SQL are "nchar", "nvarchar", and "ntext".

edited Dec 19 '12 at 15:37

answered Jun 28 '10 at 07:01

Joel May

11
1

Stimy said the data type is nvarchar. – Concrete Gannet Apr 11 '12 at 03:26

SQLMenace · Answer 7 · 2009-04-17T16:18:24.523

1

SSMS will not display that correctly, you might see question marks or boxes

paste the results into word and they should be in Japanese

In the webpage you need to set the Content-Type, the code below will display Chinese Big5

<META HTTP-EQUIV="content-type" CONTENT="text/html; charset=big5">

To verify the data you can't use ascii since ascii only can see the ascii character set

run this

select unicode(address),ascii(address) from blah where key_ID = 1

Output should be the following (it only looks at the first character) 12473 63

edited Apr 17 '09 at 16:18

answered Apr 17 '09 at 16:11

SQLMenace

132,095
25
206
225

No, it's definately storing question marks (character 63). So it's not just a display issue. – Stimy Apr 17 '09 at 16:13
that is because ascii can't see unicode, run this to verify select unicode(address),ascii(address) from blah where key_ID = 1 – SQLMenace Apr 17 '09 at 16:17
It's returning 63 for both. Which is the ? character. – Stimy Apr 17 '09 at 16:27

score 0 · Answer 8 · answered Apr 04 '17 at 19:09

We are using Microsoft SQL Server 2008 R2(SP3). Our table collation is specified as SQL_Latin1_General_CP1_CI_AS. I have my types specified as the n variety

nvarchar(MAX)
nchar(2)

..etc

To insert Japanese characters I prefix the string with a capital N

N'素晴らしい一日を'

Works like a charm.

Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?

8 Answers8

Linked

Related