Search for accentuated char in mysql

Question

I need to find occurences of é ou è in a mysql table.

First tried : SELECT "LéL" like "%é%"; which doesn't work : it returns word containing e or é or è.

I also tried with regex without results (using the the letter L):

SELECT "Lbc" REGEXP ".*L.*"; -- works : it finds L in the string LBC SELECT "Lbc" REGEXP ".*\u4C.*"; -- doesn't work : can't find the hexadecimal equivalent of L in the string LBC.

I wanted to test the \u plus hexadecimal search... I also tried double escaping as mentionned in the doc without changes.

Regex searching seems to be really limited in mysql. Didn't find any doc related to \u on http://dev.mysql.com/doc/refman/4.1/en/regexp.html.

I'm using mysql 4.1.

If there is a solution in mysql 5 which doesn't exist in 4, I would like to know about it.

why can't you just send the actual character instead of the hex code? — longneck
– longneck, Commented Sep 4, 2009 at 18:41
Well I'll be damned... That's the first thing I tried... but now that I tried again after reading your comment... It works. — Philippe Carriere
– Philippe Carriere, Commented Sep 4, 2009 at 18:45
Oh wait. I know why : I first tried LIKE "%é%" and that (!!!) doesn't work. It return normal e as well as é or è. But the regex version works for an unkown reason. — Philippe Carriere
– Philippe Carriere, Commented Sep 4, 2009 at 18:49
Was still worth to ask the question... I learned something about SQL. I learned that I don't like the "like" keyword. — Philippe Carriere
– Philippe Carriere, Commented Sep 4, 2009 at 18:55

ax. · Accepted Answer · 2009-09-04 19:36:20Z

SELECT 'LéL' LIKE '%é%' COLLATE utf8_bin; /* latin1_bin if your data is in latin1, etc. */ 1 SELECT 'LéL' LIKE '%e%' COLLATE utf8_bin; 0

see http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like and http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html

tested in MySQL 5.1 - should work in 4.1, too.

+1 for good answer but Longneck has has simpler solution. (waiting for him to post his comment as an answer)

Collectives™ on Stack Overflow

Search for accentuated char in mysql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related