- Select Collation while creating database
- Select Column Type for Urdu and Multilanguage Characterset
- SQL Statements to support Urdu and Multilanguage
Selection of Correct Collation for SQL Server Database
For Urdu characters I have used SQL_Latin1_General_CP1_CI_AS collation which is also an SQL Server default collation. This collation fully support Urdu Language including sorting and searching. Therefore you don't need to define any collation while creating Database. SQL Server will automatically choose correct collation for Urdu Language. For any other language kindly check Microsoft documentation to choose correct collation. Be careful while selecting collation because wrong selection will convert your data into garbage. Collation can be selected only at the time of creation of database and can not be changed later on. But you can also specify column level collation. For the list of collation you can check this link : http://msdn.microsoft.com/en-us/library/ms144250(SQL.90).aspx
Selection of Column Type for Urdu / Multilanguage Support
For Urdu and other Multilanguages support SQL Server provide few specific column types. These column type are nchar, nvarchar and ntext. These three column types fully support unicode characterset and hence Urdu and multilanguage. nchar is multilanguage version of char, nvarchar for varchar and ntext for text datatype. For columns which are required to hold Urdu or multilanguage characters please define either one of the three datatypes which is best suitable according to the data length.
SQL Statements to support Urdu and Multilanguage
There is not any big difference while writing SQL statement to retrieve or save multilanguage characters into database. For each type of statement (Select, Insert and Update) only character "N" is incremented before multilanguage data. Which tells SQL Sever that subsequent data after "N" is multilanguage characters. I try to explain each statement with an example.
Consider table structure with name tblTraslation:
EnglishName varchar(20)
UrduName nvarchar(20)
Column UrduName will contian Urdu language characters.
INSERT
In below statements I have marked changes with Red color which are required to insert Urdu and Multilanguage characters in database.
INSERT INTO tblTranslation (EnglishName, UrduName) Values ('Asif', N'آصف')
INSERT INTO tblTranslation (EnglishName, UrduName) Values ('Sohail', N'سہیل')
INSERT INTO tblTranslation (EnglishName, UrduName) Values ('Kamran', N'کامران')
SELECT
In below statements I have marked changes with Red color which are required to retrieve Urdu and Multilanguage characters from database.
SELECT EnglishName, UrduName from tblTranslation
SELECT EnglishName, UrduName from tblTranslation Where EnglishName = 'Asif'
SELECT EnglishName, UrduName from tblTranslation Where EnglishName Like 'As%'
SELECT EnglishName, UrduName from tblTranslation Where UrduName = N'کامران'
SELECT EnglishName, UrduName from tblTranslation Where UrduName Like N'کا%'
UPDATE
In below statements I have marked changes with Red color which are required to update Urdu and Multilanguage characters in database.
UPDATE tblTranslation SET EnglishName = 'Faraz' Where EnglishName = 'Kamran'
UPDATE tblTranslation SET UrduName = N'فراز' Where EnglishName = 'Faraz'
UPDATE tblTranslation SET EnglishName = 'Kamran' Where UrduName = N'فراز'
UPDATE tblTranslation SET UrduName = N'کامران' Where EnglishName = 'Kamran'
See how easy it is to insert and retrieve Urdu and other Language characters into SQL Server database. Hopefully it will make you clear how to use SQL Server for Urdu and Multilanguage.
