Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Arabic we have characters like ا (alef) and أ (alef with hamza).

Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How can I make SQL treat them as the same character?

I thought to replace any أ (alef with hamza) with ا (alef) at insertion but we have a lot of alternatives in Arabic language not just ا (alef) and أ (alef with hamza).

I tried Arabic_CI_AS and Arabic_CI_AI but that doesn't solve the problem.

Here is a script to regenerate the issue:

CREATE TABLE [dbo].[TestTable] (
    [ArabicChars] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];


INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');

SELECT * 
FROM TestTable 
WHERE ArabicChars like N'ا%';

The result is:

ArabicChars 

احمد

(1 row(s) affected)

The desired result would be both of the rows we inserted.

share|improve this question
    
@Nick chammas thanks for contribution –  George Botros Feb 29 '12 at 22:55
    
No problem. Aaron Bertrand has a nice little script that you can adapt to test out all possible collations. However, I suspect no collation will consider those two characters the same. –  Nick Chammas Feb 29 '12 at 23:04
    
but you're having two different chars in the the names stated, atleast in appearance. And of course, I think they should be treated as different chars ا and أ –  nuux Mar 1 '12 at 16:06
3  
@NickChammas as you guessed SOUNDEX() return 0000 for any Arabic character –  George Botros Mar 2 '12 at 12:04
1  
@gbn - Given that these are different letters, I'd say the issue is user education. If users want those letters to be treated equally -- especially in a search -- then that functionality needs to be explicitly built. It is not a collation issue. –  Nick Chammas Mar 26 '12 at 15:04
show 8 more comments

2 Answers 2

up vote 1 down vote accepted

i did few tests and i guess it is a work around but can get your job done, since SQL it self isn't helping much.

if you notice that the unicodes of these characters are close to each other

select unicode(N'أ')
  = 1571

select unicode(N'ا')
  = 1575

select unicode(N'إ')
  = 1573

so between أ and ا , its from 1571 to 1575 or if you want to make sure you get every thing in between

make sure you include from 1569 to 1575

which are

Select NCHAR(1569) = ء
Select NCHAR(1570) = آ
Select NCHAR(1571) = أ
Select NCHAR(1572) = ؤ
Select NCHAR(1573) = إ
Select NCHAR(1574) = ئ 
Select NCHAR(1575) = ا

So to make sure that you include every thing similar in your search you can use regular expressions

SELECT * 
FROM TestTable 
WHERE ArabicChars like '%[ء-ا]%'

so in this case you get all characters between ء and ا which include all those between 1569 to 1575

so in this case if your table has

 CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,
) 
INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');

the query above will get them all.

but you will notice something funny

if you have your column as a primary key

CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

you wont be able to insert these 2 records

INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');
INSERT INTO TestTable values (N'ءحمد');

because the ء,أ,إ are all to SQL are part of hamza which is ء

So if you run the query

SELECT * 
FROM TestTable 
WHERE ArabicChars like 'ء%'

it will show you

أحمد
إحمد

so to get the long story short

to SQL أ is not = to ا because its 2 different letters hamza and alefp

but ء = آ = أ = ؤ = إ = ئ

they are all Hamza ء

share|improve this answer
    
Great work @AmmarR –  George Botros May 17 '12 at 10:47
add comment

this is one of the most complicated issues i have passed through

so i will write you all what i tried that didnt work, may be you can start after that

 CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

i created your column using COLLATE Arabic_CI_AI where CI = case insensitive and AI = accent insensitive, and this is where its suppose to work because if you chose another language like for example S and Š, it works

i also tried changing the database collation to Arabic_CI_AI still didn't work

you can also collate the script like

SELECT * FROM TestTable WHERE ArabicChars COLLATE Arabic_CI_AI like 'ا%' COLLATE Arabic_CI_AI;

and it still didn't work

check out this article it speaks about the same issue but from sorting point

http://technet.microsoft.com/en-us/library/cc295829(SQL.90).aspx

this is taken from the article

For example, a sort order defines whether the Arabic character '' is less than, equal to, or greater than ''. It also defines whether the collation is accent-sensitive (for example, whether '' is equal or is not equal to '').

here is another person who researched this problem but couldnt find any solution http://blogs.msdn.com/b/michkap/archive/2008/11/11/9056745.aspx

trying to ignore diacritics or hamza i guess isnt possible in sql server currently

may be future versions

share|improve this answer
    
Good Work @AmmarR –  George Botros May 16 '12 at 22:35
    
Thanks George botros –  AmmarR May 17 '12 at 6:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.