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 d开发者_如何学运维iacritics or do I have to remove all the diacritics in my fields manually?

It's a bit like case-insensitivity problem.

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

Just convert both strings to diacritic-free before comparing.

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.

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

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.

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.