Query database for a word containing French accent

Question

i have a table in my database similar to this one

id  |  name     |  email
-----------------------------------------
1   |  elie     |  elie@company.com
2   |  jénifer  |  jenifer@company.com
3   |  jenny    |  jenny@company.com

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?

https://stackoverflow.com/questions/4024072/how-to-remove-accents-and-all-chars-a-z-in-sql-server may be it will help. — Arsalan Akhtar, May 25 '18 at 06:49
@ArsalanAkhtar, he wants to retain the accents and also that question is for SQL Server which he hasn't indicated if he is using. — Nigel Ren, May 25 '18 at 06:51
@ArsalanAkhtar the link you mentioned is doing the oppositeof what I'm asking. i need to keep the accents letters and return the exact match from the Database — Joey Azar, May 25 '18 at 06:59

score 0 · Answer 1 · answered May 25 '18 at 07:16

I have founded that its known bug in mysql 5.1

Mysql bug

[1 Nov 2010 8:52] Peter Laursen So you may create the table like this

CREATE TABLE test (
 id INT AUTO_INCREMENT,
 vers TEXT,
 PRIMARY KEY (id)
) CHARACTER SET utf8 COLLATE utf8_danish_ci;

or this

CREATE TABLE `test` (
 `id` INT (11) NOT NULL AUTO_INCREMENT,
 `vers` TEXT CHARACTER SET utf8 COLLATE utf8_danish_ci,
 PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

or

write queries with a COLLATE clause(s) as documented here: http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html

Note that LIKE operator does not support COLLATE. It executes but is ignored.

SELECT 'a' LIKE 'å' COLLATE utf8_danish_ci; -- '0'

(what I think I will report as a bug myself or doc request at least)

Thank you. What i understand that this is a bug and we can't solve it? i tried changing the collation to utf8_danish_ci with no success. So far i can get the correct result only if i change it to latin1_general_ci. But this collation will give error entries when trying to add a record containing arabic letters from my php files — Joey Azar, May 25 '18 at 07:41

score 0 · Accepted Answer · answered May 25 '18 at 07:30

0

Use REGEXP for it

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

LIKE ignores diacritics while REGEXP does not.

answered May 25 '18 at 07:30

Bernhard

1,852
11
19

Query database for a word containing French accent

2 Answers2