Accent insensitive search in InnoDB MySQL table!

Question

Hello,

I am working on a simple search script that looks through two columns of a specific table. Essentially I'm looking for a match between either a company's number or their name. I'm using the LIKE statement in SQL because I am using InnoDB tables (which means no fulltext searches).

The problem is that I am working in a bilingual environment (french and english) and some of the characters in french have accents. I would like accented characters to be considered the same as their non-accented counterpart, in other words é = e, e = é, à = a, etc. SO has a lot of questions pertaining to the issue but none seem to be working for me.

Here is my SQL statement:

SELECT id, name FROM clients WHERE id LIKE '%éc%' OR name LIKE '%éc%';

I would like that to find "école" and "ecole" but it only finds "école".

I would also like to note that my tables are all utf8_general_ci.

Help me StackOverflow, you're my only hope! :)

Answer 1

You maybe should consider converting the problem characters to their English counterparts, then storing them in a different column, perhaps called searchable or similar. You would of cause need to update this whenever your main column was updated.

You would then have two columns, one containing the accented characters and one containing the plain English searchable content.

Answer 2

I am going to offer up another answer for you.

I just read that utf8_general_ci is accent-insensitive so you should be OK.

One solution is to use

 mysql_query("SET NAMES 'utf8'"); 

This tells the client what char set to send SQL statements in.

Another solution seems to be to use MySQL's HEX() function to convert the accented chars into their Hex value. But I could not find any good examples of this working and after reading the MySQL docs for HEX() it looks like it probably will not work.