Search code examples
phpsqldatabasefrench

Query database for a word containing French accent

i have a table in my database similar to this one

id  |  name     |  email
-----------------------------------------
1   |  elie     |  [email protected]
2   |  jénifer  |  [email protected]
3   |  jenny    |  [email protected]

as you can see the record with ID=2 has in the name a french character. when running this SQL

SELECT * FROM `TABLENAME` WHERE `name` LIKE '%jé%'

I'm expecting to see only the record with ID=2. Instead I'm getting ID=2 & ID=3. the SQL is replacing the french character "é" with "e".

My Database, table and fields have Collation=utf8_general_ci

what should i do in this case to get the correct result if i need to keep the french characters saved in my database as well?

Solution

  • Use REGEXP for it

    SELECT * FROM `TABLENAME` WHERE `name` REGEXP '.*é.*'

    LIKE ignores diacritics while REGEXP does not.