Long time ago, I have written a blog which talks about multi language support available with SQL Server.
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 (http://blog.sqlauthority.com)