Solved

How do i perform an arabic search in SQL Server ignoring diacritics?

Posted on 2008-06-22
9
2,165 Views
Last Modified: 2012-06-22
Hi,

I have SQL Server 2005 and using a database with arabic collation.  Certain fields have english and others have arabic (there are no mixed languages, strictly one language per column).

In the arabic field I would like to store words with diacritics.  So for those who know arabic, instead of storing #-E/ I will store #N-REN/Q .  For those who know english, perhaps the closest example is instead of storing Pierre in the database, I will stored PieNrre (perhaps not correct, but you get the idea).

So my question how do i do an SQL search that will exclude diacretics?  Perhaps in a Web form my user will type in a search for '#-E/', however SELECT * FROM tableName WHERE word LIKE '%#-E/%' doesn't work.

I have been searching around the web but can't really get a head start on this.  
0
Comment
Question by:almuallim
  • 5
  • 4
9 Comments
 

Author Comment

by:almuallim
Comment Utility
It seems arabic characters did not appear properly in my post. I'm attaching an SQL sample query.
SELECT * 

FROM dbo.ArabicPlurals

WHERE Singular LIKE ('%#-E/%')

Open in new window

0
 

Author Comment

by:almuallim
Comment Utility
Attaching a txt file to see if works!  Will need arabic support to view properly.
arabicdiacriticQuestion.txt
0
 
LVL 4

Expert Comment

by:cavehop
Comment Utility
You will want to use "COLLATE <collation>" in your SQL query. This keyword follows the column whose collation you wish to explicitly specify.

So for example, if you want to use Arabic collation that is case insensitive and accent insensitive (i.e. don't pay attention to diacritics), you would do something like:

SELECT * FROM dbo.ArabicPlurals
WHERE Singular COLLATE Arabic_CI_AI LIKE ('some arabic string')

Similary, case sensitive, accent sensitive is Arabic_CS_AS.

You can find a list of the collations supported by SQL Server here: http://www.collation-charts.org/mssql/
0
 
LVL 4

Expert Comment

by:cavehop
Comment Utility
Oh, I should also mention that you can specify the collation for a particular column, so that collation will be used as the default for all queries.

To do this enter "COLLATE <collation" immediately after the datatype in the CREATE or ALTER TABLE query.

e.g. (obviously, use whatever datatype your column really is)
ALTER TABLE dbo.ArabicPlurals
ALTER COLUMN Singular NVARCHAR(40) COLLATE Arabic_CS_AI
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:almuallim
Comment Utility
Hi cavehop,

Thanks for your response.  I tried using the accent and case insensitive arabic collation and although it makes a difference it only seems to ignore the end diacritic, so my word which has a diacritic on each letter is not found.

Changing the collation definitely makes a difference though....I'm not sure whether Arabic_CS_AI would be beneficial only when sorting as is implied by some articles.



 
0
 
LVL 4

Expert Comment

by:cavehop
Comment Utility
Unfortunately, I only know the general principle of how the collation works; I'm not at all knowledgable about Arabic. The behavior you describe sounds awfully like a bug to me.

You probably have already thought to test this but, is it always just the end diacritic that is ignored? Or is it certain characters that are not being ignored when they should be (i.e. try swapping a couple of the letters and see how it behaves)?
0
 

Accepted Solution

by:
almuallim earned 0 total points
Comment Utility
Thanks for your help anyway.  Would still like it if someone has an idea on how to go about this, even if it's complex.

Having said that, i found the following article http://www.experts-exchange.com/Database/Miscellaneous/Q_21276274.html.  The solution might just be to have one column with diacritics and one without.  
0
 
LVL 4

Expert Comment

by:cavehop
Comment Utility
Here's something else I just came across that may or may not be pertinent, but seems like it might be.

According to an article on Arabic language support on the MSDN SQL Server Developer Center website:

"When dealing with Unicode string constants in SQL Server 2005, you must precede all Unicode strings with a capital letter N. The N prefix stands for National Language in the SQL-92 standard, and must be uppercase. If the N prefix is not used, SQL Server uses the non-Unicode code page of the current database before it uses the string."

This would mean your query should look something like:
SELECT * FROM dbo.ArabicPlurals
WHERE Singular COLLATE Arabic_CI_AI LIKE (N'some arabic string')

I don't know whether or not this will make any difference, but it's simple enough to try quickly anyway.

Also, the article this comes from has all kinds of information about support for Arabic in SQL Server: http://msdn.microsoft.com/en-us/library/cc295829.aspx
0
 

Author Comment

by:almuallim
Comment Utility
Hi,

I tried it and it still doesn't work. I will take a look at that article and see if there is any mention of anything else.

Thanks again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now