CODEWITHSUNDEEP
phpsqlmysqldiacritics
maccen
maccen

Reputation: 155

Diacritic insensitive mysql search?

How do I make a diacritic insensitive,

ex this persian string with diacritics

هواى بَر آفتابِ بارِز

is not the same as with removed diacritics in mySql

هواى بر آفتاب بارز

Is there a way of telling mysql to ignore the diacritics or do I have to remove all the diacritics in my fields manually?

Upvotes: 3

Views: 3087

Answers (5)

Marcos
Marcos

Reputation: 111

The cleanest solution I've come to is:

SELECT arabic_word 
FROM Word
WHERE ( arabic_word REGEXP '{$search}' OR SOUNDEX( arabic_word ) = SOUNDEX( '{$search}' ) );

I haven't checked the costs of the SOUNDEX function. I guess this could for small tables, but not for large datasets.

Upvotes: 0

vsen
vsen

Reputation: 21

I'm using utf8 (utf8_general_ci) and searching arabic without diacritics doesn't work, it isn't insensitive or it is but don't work properly.

I tried looking at the character with and without a diacritic using Hex and it looks like mysql considering it as two distinct character.

I'm thinking about using hex and replace (a lot of replace) to search for words while filtering diacritics.

my solution to have an insensitive search for arabic words:

SELECT arabic_word FROM Word
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(HEX(REPLACE(
arabic_word, "-", "")), "D98E", ""), "D98B", ""), "D98F", ""), "D98C", 
""),"D991",""),"D992",""),"D990",""),"D98D","") LIKE ?', '%'.$search.'%'

the values formatted in hexadecimal are diacritics that we want to filter. ugly but I didn't find another anwser.

Upvotes: 2

khel
khel

Reputation: 2368

Setting

set names 'utf8'

before making a query usually does the trick for latin lookups. I'm not sure if this works for arabic as well.

Upvotes: 0

jitter
jitter

Reputation: 54605

Did you already read all of MySQL Character Set Support to check if the answer to your question isn't already there? Especially collations are to be understood.

I wild guess is that using utf8_general_ci could do the right thing for you

Upvotes: 0

Artelius
Artelius

Reputation: 49089

It's a bit like case-insensitivity problem.

SELECT * FROM blah WHERE UPPER(foo) = "THOMAS"

Just convert both strings to diacritic-free before comparing.

Upvotes: 3

Related Questions