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)
I regularly visit your blogs. I found many important things many times. Is there a way to create language converter function in sql server?
I don’t think its easy to write translator using T-SQL. you can use .NET or CLR to do that.
Thx alot sir
Not working Tamil font in your insert Query
can you update?
You should use like this
Insert into MultiLanguage_Will_Not_Work values (2, ‘without N’, N’என் பெயர் PINAL ஆகிறது’)-
can you help me to find chinese characters data from table.?
below ismy query. i want heading in gujarati. can you please help me.
declare @Query1 varchar(max)
SET @Query1 =’
select ReferenceNo as ‘N’આવક સંદર્ભ”
from GovernmentReferenceDashboard where 1=1’
I want to search a table and retrieve the records where there’s hindi content how do I do that ?