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:

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

  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?

    • Wasim May 30, 2019 / 7:42 pm

      SELECT EnglishName, UrduName from tblTranslation Where UrduName Like N’کا%’

  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

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

  12. Krishnagar November 1, 2018 / 12:30 pm

    IT’S WORK Thanks!!!!

  13. Ahsan Khan December 4, 2018 / 12:32 pm

    Very helpful
    Thanks

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

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

  16. Mathanraj December 25, 2020 / 2:36 pm

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

Leave a Reply

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