[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-06-22
9
Medium Priority
?
2,639 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
ID: 21840403
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
ID: 21840425
Attaching a txt file to see if works!  Will need arabic support to view properly.
arabicdiacriticQuestion.txt
0
 
LVL 4

Expert Comment

by:cavehop
ID: 21842523
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 4

Expert Comment

by:cavehop
ID: 21842542
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
 

Author Comment

by:almuallim
ID: 21843921
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
ID: 21845956
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
ID: 21853785
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
ID: 21858590
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
ID: 21863473
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 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