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:
- How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
- What is the collation I need to use if I have to store Tamil in SQL Server tables?
- 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
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)
41 Comments. Leave new
Helpful…Tks Sir
Your welcome @Rao
Good Explanation,
could you explain about temp table and table variables and common table expression in deeply?
Thanks
Karthik
I already have few blogs on the same topic. Search on search.sqlauthority.com
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.
C#:
DataSet ds = new DataSet();
ds.ReadXml(“path”);
Then when we are inserting chinese lan, just before the sentence give ‘N’,
Thanks
Karthik
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?
Yes, You can. Make sure N is capital before the string. like
N’ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ’
you should not use n’ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ’
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
Your welcome @Rakesh
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.
Hi SHRAVAN,
Thanks for the comments. PL/SQL is oracle thing. If you can provide sample, someone might help you in T-SQL.
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
Diptasundar – If you use Unicode “N” then you should be able to.
How to Add N @columnName In Stored Procedure ?
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.
hello sir
is there is any way to insert multi language data with varchar datatype as it’s datatype
i want to table column name unicode in sql
how to use where (find) command in gujarati
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
not it’s not working in console panel in mysql database
HI Pinal,
Already i have english data in table. I want to write select query which display out put in Telugu(Local Language)
Hi Pinal,
Thank you very much for sharing above article, it is very helpful.
Thanks,
Narsinh
How to manage multi language by stored procedure
Have you achieved this?
if yes please suggest how.
Stored procedures are in T-SQL syntax. What do you mean by “multi language by stored procedure” ?
But Sir I have to use strore procedure. Using the input varible I have o insert.so how can I do?
Declare variables/parameters as nvarchar and also pass them correctly from application.
Your explanation is very good. thank you
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?