Ignore Arabic diacritical in MySql Text Indexing

Question

2

In mysql Database, I am storing Arabic text mainly, and using mysql match against search.

In Arabic, some different characters are used interchangeably and should be treated equally when searched for.

Ex1: the following characters (أ - إ - آ - ا) should be treated the same. I use docker should I change anything from mysql config?

Answer 1

I faced the same problem. I solved the problem using these steps:

1. Create a function to remove the diacritic and unify the similar characters

   CREATE FUNCTION AR_UNIFY(s VARCHAR(191) )
   RETURNS VARCHAR(191)
   DETERMINISTIC
   BEGIN
       RETURN REPLACE( REPLACE( REPLACE( REPLACE( s,'أ','ا'), 'ي','ى'), 'إ','ا'), 'ة','ه') ;
   END;
   

2. Use this function in the condition inside the SELECT statement like this:

   SELECT * FROM users WHERE AR_UNIFY(name) LIKE AR_UNIFY("%أإا%");