Unicode- VARCHAR and NVARCHAR

Question

-- Creating Table 

  Create Table Test1
  (
    id Varchar(8000)
  )

-- Inserting a record  
Insert into Test1 Values ('我們的鋁製車架採用最新的合金材料所製成，不但外型輕巧、而且品質優良。為了達到強化效果，骨架另外經過焊接和高溫處理。創新的設計絕對能充分提升踏乘舒適感和單車性能。');

As I have defined data type of id as Varchar. The data is stored as ?????.

Do I have to use NVARCHAR..? What is Difference between VarChar and Nvarchar(). Please explain about UNIcode as well.

You will need to use the `N` prefix on the values as well. `N'我們的鋁製車架'` — Martin Smith, Oct 21 '11 at 19:33
Possible duplicate of [varchar or nvarchar](http://stackoverflow.com/questions/1271313/varchar-or-nvarchar) — Kzqai, Oct 11 '16 at 22:48

score 15 · Accepted Answer · answered Oct 21 '11 at 19:14

15

The column type nvarchar allows you to store Unicode characters, which basically means almost any character from almost any language (including modern languages and some obsolete languages), and a good number of symbols too.

answered Oct 21 '11 at 19:14

Mark Byers

811,555
193
1,581
1,452

i have tried this but at times it inserts the funny language letters and sometimes it doesnt - data type is set to nvarchar. – mjcoder Mar 28 '13 at 10:40
1

@c-sharpnewbie then you have an encoding problem somewhere. Ask a specific question about that. – Jeroen Wiert Pluimers Dec 23 '13 at 18:16
One note. It seems that NVARCHAR uses two bytes to store charactes, for a total of 64k combinations. However, as we speak Unicode has around 150k characters. That means that NVARCHAR is probably only representing a subset of Unicode. – The Impaler Feb 07 '23 at 14:05

score 8 · Answer 2 · answered Sep 10 '15 at 18:57

also it is required to prefix N before your value. example Insert into Test1 Values (N'我們的鋁製車架採用最新的合金材料所製成，不但外型輕巧、而且品質優良。為了達到強化效果，骨架另外經過焊接和高溫處理。創新的設計絕對能充分提升踏乘舒適感和單車性能。'); or programatically use preparedstatement with bind values for inserting and updating natural characterset

score 4 · Answer 3 · answered Oct 21 '11 at 19:14

4

Nvarchar supports UNICODE. SO yes. you need to have the column as nvarchar and not varchar.

answered Oct 21 '11 at 19:14

Vivek Viswanathan

1,968
18
26

It seems that NVARCHAR uses two bytes to store charactes, for a total of 64k combinations. However, as we speak Unicode has around 150k characters. That means that NVARCHAR is probably only representing a subset of Unicode. – The Impaler Feb 07 '23 at 14:06

score 3 · Answer 4 · answered Dec 21 '17 at 07:55

3

Despite the collation of your database. Use nvarchar to store UNICODE. Embbed your Unicode value in N'[value]'

INSERT INTO ... VALUES
('Azerbaijani (Cyrillic)', N'Aзәрбајҹан (кирил әлифбасы)', 'az-cyrl')

In DB: 59   Azerbaijani (Cyrillic)  Aзәрбајҹан (кирил әлифбасы) az-cyrl

Important is the N prefix!

Valid for MS SQL 2014 I am using. Hope this helps.

answered Dec 21 '17 at 07:55

Andreas Ahlen

31
3

Thank you sooooooo much for mentioning the N-strings, it helped me fix my problem. – Arthur Khazbs Jun 05 '21 at 13:06

score 2 · Answer 5 · answered Oct 21 '11 at 19:13

2

Yes you have to use nvarchar or use a collation for the language set you want. But nvarchar is preferred. Goodgle can tell you what this stuff means.

answered Oct 21 '11 at 19:13

HLGEM

94,695
15
113
186

score 0 · Answer 6 · answered Jul 07 '16 at 01:01

Varchar uses Windows-1252 character encoding, which is for all practical purposes standard ASCII.

As others have noted, nvarchar allows the storage of unicode characters.

You can get the ASCII translations from either data type, as shown here:

IF OBJECT_ID('TEST1') IS NOT NULL
  DROP TABLE TEST1
GO
CREATE TABLE TEST1(VARCHARTEST VARCHAR(8000), NVARCHARTEST NVARCHAR(4000))

-- Inserting a record  
INSERT INTO TEST1 VALUES ('ABC','DEF')
SELECT
  VARCHARTEST
 ,NVARCHARTEST
 ,ASCII(SUBSTRING(VARCHARTEST,1,1))
 ,ASCII(SUBSTRING(VARCHARTEST,2,1))
 ,ASCII(SUBSTRING(VARCHARTEST,3,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,1,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,2,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,3,1))
FROM
  TEST1
DROP TABLE TEST1

Unicode- VARCHAR and NVARCHAR

6 Answers6

Linked