Friday, May 29, 2009

Working with Sinhala Letter in SQL Server

Being a Sri Lankan and SQL Server professional, I would love to see Sinhala font in SQL Server.

In SQL Server 2005 there is a matching collation for Sinhala called Indic_General_90_BIN. In SQL Server 2008,  there is a collation called Indic_General_100_BIN
Refer http://msdn.microsoft.com/en-us/library/ms188046.aspx

DROP TABLE fruits

CREATE TABLE Fruits

    (

      NAME NVARCHAR(50) COLLATE Indic_General_90_BIN

    )

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අඹ')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කොස්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නාරං')           

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'දං')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'ඇපල්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'කෙසෙල්')

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අන්නාසි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'ලාවලු') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කැකිරි') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'රඹුටන්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  ( N'දෙලුම්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නෙල්ලි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අලි පේර')

           

You can order them by the Sinhala alphabet. 

SELECT  * FROM    [Fruits] ORDER BY NAME

image

It also works with normal searching operations such as = and like.

SELECT * FROM Fruits WHERE NAME = N'රඹුටන්'

image

SELECT * FROM Fruits WHERE NAME Like N'%'

image

SELECT * FROM Fruits WHERE NAME Like N'%'

image

2 comments:

  1. Could you please suggest a way to insert data using a front end like c# application. I tried many ways. But it goes to database as ?????? marks. When they are retrieved to a textbox or somewhat like that it too shows the same. Collate technique you used didn't work as well. Thank you

    ReplyDelete
  2. What is the sinhala collation for Sql 2012

    ReplyDelete