CODEWITHSUNDEEP
sqlsql-server
Hari Gillala
Hari Gillala

Reputation: 11926

Unicode- VARCHAR and NVARCHAR

-- 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.

Upvotes: 12

Views: 26883

Answers (6)

Andreas Ahlen
Andreas Ahlen

Reputation: 31

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.

Upvotes: 3

JosephStyons
JosephStyons

Reputation: 58785

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

Upvotes: 0

Rajesh Kumar
Rajesh Kumar

Reputation: 81

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

Upvotes: 8

Mark Byers
Mark Byers

Reputation: 839114

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.

Upvotes: 15

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

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

Upvotes: 4

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 2

Related Questions