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

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 –

  1. 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.
  2. 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.
  3. 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'%एक%';

Unicode Results
Unicode Results

Further Reading:

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

    • ayana asefa March 15, 2017 / 9:12 am

      thanks. it is work

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

  2. geetha January 8, 2013 / 6:49 am

    thanks very useful

  3. Kie Veasna February 6, 2013 / 12:30 pm

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

  4. rakesh October 15, 2013 / 8:54 am

    very very thanks

  5. abhi February 16, 2014 / 3:58 am

    thenks but dynamic string data not supported

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

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

    • Aalam Rangi December 20, 2014 / 12:20 pm

      Could you try increasing the width of the variables or columns?

  8. Rajesh Kumar Maurya April 16, 2015 / 3:17 pm

    nice, article….

  9. Vijay Soni November 30, 2015 / 12:22 am

    Many thanks!!!

    • 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!

  10. Jasbana October 31, 2016 / 9:34 am

    thankyou

Leave a Reply

Your email address will not be published. Required fields are marked *