SQL SERVER – Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Long time ago, I have written a blog which talks about multi language support available with SQL Server.

SQL SERVER – 2005 – Multiple Language Support

I have been getting below questions on regular basics and it still baffles me:

  1. How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
  2. What is the collation I need to use if I have to store Tamil in SQL Server tables?
  3. I am inserting the data in Gujarati but while selecting I am getting “???”

Here are some basic rules which you may want to keep in mind. While storing Unicode data, the column must be of Unicode data type (nchar, nvarchar, ntext). Another rule is that the value must be prefixed with N while insertion.

If you are using a stored procedure then while passing the parameter to stored procedure make sure that N is used before the string. Also make sure the parameter declared within the stored procedure which are used for carrying those variable are also defined as Unicode.

Here is the sample script and the output which can make sure understand the things. I have used Google translate to do translation and I am not sure how accurate it is in Kannada, Tamil and Telugu.

use master
go
If db_id('SQLAuthority') is not null
drop database SQLAuthority
go
set nocount on
go
Create database SQLAuthority
go
use SQLAuthority
go
Create Table MultiLanguage_Will_Not_Work (i int, j varchar(10), k varchar(100))
go
-- Hindi
Insert into MultiLanguage_Will_Not_Work values (1, 'with N', N'मेरा नाम Pinal है')
go
-- Tamil
Insert into MultiLanguage_Will_Not_Work values (2, 'without N', 'என் பெயர் PINAL ஆகிறது')
go
Select * from MultiLanguage_Will_Not_Work
go
Create Table MultiLanguage (i int,j varchar(10), k nvarchar(100))
go
-- Gujarati
Insert into MultiLanguage values (1, 'with N', N'મારું નામ પિનલ છે')
go
-- Telugu
Insert into MultiLanguage values (2, 'without N', 'నా పేరు PINAL ఉంది')
go
-- Kannada
Insert into MultiLanguage values (3, 'with N', N'ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ')
go
Select * from MultiLanguage
go
use master
go
drop database SQLAuthority
go

Here is the output

SQL SERVER - Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table unicode-01

Same would work for any language as well. It is important for us to know the basics and this blog post is one of many basics blogs that I plan to write. Do let me know which topic you would like me to revisit as part of this – back to basics series? More ideas you give me, more interesting the series can be.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Configure, Monitor and Restore Backups from Mobile & Web browser
Next Post
Hey DBA – Protecting a Proactive Attitude – Notes from the Field #069

Related Posts

40 Comments. Leave new

  • Helpful…Tks Sir

    Reply
  • Good Explanation,
    could you explain about temp table and table variables and common table expression in deeply?

    Thanks
    Karthik

    Reply
  • That is ok when we are inserting values manually but i have requirement that i should read data from XML tag (tag contains chinese data) and store that in a db column. can you please help me out in this.

    Reply
    • C#:
      DataSet ds = new DataSet();
      ds.ReadXml(“path”);
      Then when we are inserting chinese lan, just before the sentence give ‘N’,

      Thanks
      Karthik

      Reply
  • Hi Kunal,

    Thanks very much for sharing the article, nice one.
    I am just wondering whether we would be able to query on tamil, hindi fonts using normal where statement (select * where name like n’என் பெயர் PINAL ஆகிறது’), will these have any the effect on full text index and search query?

    Reply
    • Yes, You can. Make sure N is capital before the string. like
      N’ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ’
      you should not use n’ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ’

      Reply
  • Hi Pinal,
    I’ve always read and heard about it but never seen it in action.

    Thnx to your blog post.It’s helpful.

    Regards,
    Rakesh

    Reply
  • Thanks Pinal Ji,
    I regularly visit your blogs. I found many important things many times. I want to convert decimal to binary through loop. could provide some script in PL/SQL.

    Reply
    • Hi SHRAVAN,
      Thanks for the comments. PL/SQL is oracle thing. If you can provide sample, someone might help you in T-SQL.

      Reply
  • Diptasundar Sahoo
    May 19, 2015 2:28 pm

    Dear Pinal Sir,
    I watch your blog regularly. I get a lots of help from you. My Question is..
    I can easily insert hindi text into the database using query. But i am unable to insert hindi text into the db using parametrized store procedure

    Reply
  • How to Add N @columnName In Stored Procedure ?

    Reply
    • We can save data in stored procedure without writing N @columnName
      Instead declare variable with NVARCHAR(MAX) within the store procedure that gets @columnName value from start to end in the SP and make sure you wont put this value in a variable with “VARCHAR” or else the unicode wont work.

      Hope it works for you.

      Reply
  • hello sir
    is there is any way to insert multi language data with varchar datatype as it’s datatype

    Reply
  • i want to table column name unicode in sql

    Reply
  • how to use where (find) command in gujarati

    Reply
  • Hi Pinal,
    I am using a stored procedure
    INSERT INTO [dbo].[usp_language_text]
    (language_id
    ,text)
    VALUES
    (
    @language_id
    ,N”+@text
    )

    here it is not working……………………….can u please help me?
    Thanks in advance dear

    Reply
  • not it’s not working in console panel in mysql database

    Reply
  • HI Pinal,
    Already i have english data in table. I want to write select query which display out put in Telugu(Local Language)

    Reply
  • Hi Pinal,

    Thank you very much for sharing above article, it is very helpful.

    Thanks,
    Narsinh

    Reply
  • How to manage multi language by stored procedure

    Reply
  • Have you achieved this?

    if yes please suggest how.

    Reply
    • Stored procedures are in T-SQL syntax. What do you mean by “multi language by stored procedure” ?

      Reply
  • But Sir I have to use strore procedure. Using the input varible I have o insert.so how can I do?

    Reply
  • Your explanation is very good. thank you

    Reply
  • I’m not able to properly insert gujarati font in sql server using this formula,whenever i tried to insert gujarati font it display ????…… so, how can i solve this problem?

    Reply

Leave a Reply

Menu