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".

2 Answers
2

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.

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.

I'm not sure I feel like dealing with the redundant data, especially since I might end up using this solution elsewhere in areas where content is much bigger than just a name and identifier. I know there are ways to do this in PHP so I'd be surprised if there isn't a solution do this with MySQL.
–
GazillionSep 2 '10 at 13:04