How to Store (and Retrieve) Non-English Characters (e.g. Hindi, Czech, Arabic etc.) in SQL Server

May 13, 2012June 24, 2016Aalam Rangi

Store multiple language strings in SQL Server — How to store text in multiple languages in SQL Server.

If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –

Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
The N should be used even in the WHERE clause.

REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
http://support.microsoft.com/kb/239530

If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.

The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.

DROP TABLE dbo.unicodeData;
GO

CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO

INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)

,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')

,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')

,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)

,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)

,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO

SELECT *
FROM dbo.unicodeData;
GO

-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';

Further Reading:

nchar and nvarchar (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186939.aspx
Collation and Unicode Support
http://msdn.microsoft.com/en-us/library/ms143726.aspx

megha October 31, 2012 / 8:19 am

hi,

i tried as your example

but still its showing in the data field like some square boxes

plz give some suggestion

Reply
geetha January 8, 2013 / 6:49 am

thanks very useful

Reply
Kie Veasna February 6, 2013 / 12:30 pm

I spent a few hours. Finally, I found your topic it helpful. I’m so glad.

Reply
rakesh October 15, 2013 / 8:54 am

very very thanks

Reply
abhi February 16, 2014 / 3:58 am

thenks but dynamic string data not supported

Reply
parv February 26, 2014 / 2:18 am

may i know what version of mysql support unicode..like hindi .
Because when i am inserting same code it is showing me ‘?????’
please reply parvkashyap@gmail.com

Reply
imaphpdeveloper December 20, 2014 / 3:50 am

How to fetch the data stored with N, because yes its fine while inserting but while fetching its shows some truncated values

Reply
- Aalam Rangi December 20, 2014 / 12:20 pm
  
  Could you try increasing the width of the variables or columns?
  
  Reply
- Wasim May 30, 2019 / 7:42 pm
  
  SELECT EnglishName, UrduName from tblTranslation Where UrduName Like N’کا%’
  
  Reply
Rajesh Kumar Maurya April 16, 2015 / 3:17 pm

nice, article….

Reply
Vijay Soni November 30, 2015 / 12:22 am

Many thanks!!!

Reply
- Aalam Rangi November 30, 2015 / 12:29 am
  
  You are welcome, Vijay! Are there any other topics that you’d like to read about? I always welcome new blog post ideas!
  
  Reply
Jasbana October 31, 2016 / 9:34 am

thankyou

Reply
malika May 7, 2018 / 12:00 pm

hi sir,

Iam working with oracle 11g andmy database character set is AL32UTF8.

I am able to store hindi values in a table.but I want the tablename in hindi

EXAMPLE:

create a table राकी

column names are ऋतु,अवधि
value1 गर्मी,सुबह
value2 सर्दी,शाम

please help me to create this table in oracle 11g with characterset AL32UTF8

reply me fast

Thanks

Reply
- Aalam Rangi May 8, 2018 / 2:47 am
  
  Hello,
  I’ve not worked with Oracle for some time so can’t help you with this.
  Sorry!
  You can ask your question in –
  https://stackoverflow.com/questions/tagged/oracle
  
  Reply
Krishnagar November 1, 2018 / 12:30 pm

IT’S WORK Thanks!!!!

Reply
Ahsan Khan December 4, 2018 / 12:32 pm

Very helpful
Thanks

Reply
Pramukh October 25, 2019 / 8:59 am

Hi ,

Can you please share how to store and retrieve Japanese characters ?
you have mentioned you will do it for next blog, was not able to find it .
so please help me out.

Thanks

Reply
VENKATESAN April 18, 2020 / 6:25 am

sir, i tried by storing values @ text box and collect the values at variable and inserting by query with N prefix. but stored as ???? character only.

kindly reply us to sscabusy@gmail.com please.. waiting…

Reply
Mathanraj December 25, 2020 / 2:36 pm

Unable to Insert Gujarati Words in table or in Select Query. Its shows irrelevant words. Pls suggest

Reply