Deal with Indian regional language in SQL Server 2005/2008

Yesterday, I have received one email for help about querying and filtering records contain Telugu Indian regional language. Have a look:
The problem was when person filtering records of telugu language, was not able to get any single records. You have to deal with few things while dealing with regional languages.
1.)    If whole database should be in regional language, set proper collation for that.
2.)    Set proper collation for column
3.)    While querying or inserting data, MUST USE “N” as a prefix as regional data should be in Unicode and “N” prefix is must to differentiate whether data is Unicode.
When I asked that person, what collation are you using for database and column? Below is the answer:
=========================================================================
Hi Ritish,
                Yes.I have set Indic_General_90_BIN as Proper collation to Telugu Column.
                 But I am not able to get the records.
                 Is there any way to get the records please ? 
=========================================================================
What is the reason if proper collation is set even though data doesn’t get filtered by following query?
SELECT * FROM TeluguDictionary where (Telugu like ‘%అక్క%’)


Or doesn’t insert proper data with following INSERT statement?
INSERT INTO TeluguDictionary VALUES (‘akkadi’,అక్కడి)


“N” prefix was missing in both query. If s/he would have used same query with “N” prefix like below, it would have worked.
SELECT * FROM TeluguDictionary where (Telugu like N‘%అక్క%’)
INSERT INTO TeluguDictionary VALUES (‘akkadi’,Nఅక్కడి)
Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Advertisements

12 Responses to “Deal with Indian regional language in SQL Server 2005/2008”

  1. Anuradha Says:

    Hi Ritish, I got another issue. When I have executed with stored procedure the data is not displayed. Procedure : CRETE PROCEDURE [dbo].[GetTeluguwords] @Telugu varchar(max) As SELECT * FROM TeluguDictionary WHERE (Telugu like N'%'+@Telugu+'%') Could you please solve this issue.RegardsAnuradha

  2. Anuradha Says:

    Hi Ritish, I got another issue. When I have executed with stored procedure the data is not displayed. Procedure : CRETE PROCEDURE [dbo].[GetTeluguwords] @Telugu varchar(max) As SELECT * FROM TeluguDictionary WHERE (Telugu like N'%'+@Telugu+'%') Could you please solve this issue.RegardsAnuradha

  3. Ritesh Shah Says:

    why don't you use dynamic SQL? something like this:CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') '

  4. Ritesh Shah Says:

    why don't you use dynamic SQL? something like this:CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') '

  5. Ritesh Shah Says:

    you have to execute statement at the end like this:CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') 'exec (@sql)

  6. Ritesh Shah Says:

    you have to execute statement at the end like this:CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') 'exec (@sql)

  7. Anuradha Says:

    Hi Ritish, I have executed this procedure .But the data not displayed.Please help me.

  8. Anuradha Says:

    Hi Ritish, I have executed this procedure .But the data not displayed.Please help me.

  9. Ritesh Shah Says:

    can you please run this SP? it will print query, you just give me that printed query so that I can debug more.CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') '–exec (@sql)print @sql

  10. Ritesh Shah Says:

    can you please run this SP? it will print query, you just give me that printed query so that I can debug more.CREaTE PROCEDURE [dbo].[GetTeluguwords]@Telugu varchar(max)Asdeclare @sql as varchar(max)set @sql='SELECT * FROM TeluguDictionary WHERE (Telugu like N''%'+@Telugu+'%'') '–exec (@sql)print @sql

  11. Anuradha Says:

    Hi Ritish, I have executed this stored procedure.The printed query is : USE [Telugu]GODECLARE @return_value intEXEC @return_value = [dbo].[GetTeluguwords] @Telugu = N'అ'SELECT 'Return Value' = @return_valueGO

  12. Anuradha Says:

    Hi Ritish, I have executed this stored procedure.The printed query is : USE [Telugu]GODECLARE @return_value intEXEC @return_value = [dbo].[GetTeluguwords] @Telugu = N'అ'SELECT 'Return Value' = @return_valueGO


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: