REGEXP with MySQL isn't working properly

Question

If I want to search all data with the string léon in the name field, I use a query like this:

SELECT * FROM `my_table` WHERE `name` REGEXP 'léon'

This request is OK.

Now, I need to retrieve all rows with the string léon or leon or lèon, so I tried:

SELECT * FROM `my_table` WHERE `name` REGEXP 'l[éeè]on'

This query doesn't work... I have no error, but no result. Do you have any ideas?

It works for me... Maybe a version thing? What MySQL version are you running on? — LeonardChallis, May 03 '13 at 13:12
if i try with SELECT * FROM `my_table` WHERE `name` REGEXP 'lé[o]n', it's ok. So it's not a problem with the version i think. — Clément Andraud, May 03 '13 at 13:16
Doesn't appear to work in the fiddle - http://sqlfiddle.com/#!2/78eb95/3 — sgeddes, May 03 '13 at 13:16
If you use a ISO-8859-x charset, it should work, if you use UTF-8 which is a multibyte charset, not so much. — Joachim Isaksson, May 03 '13 at 13:18
@JoachimIsaksson -- looks right: http://sqlfiddle.com/#!2/0ffcb/1 -- post as answer! — sgeddes, May 03 '13 at 13:23
@JoachimIsaksson: are you saying that regular expressions don't work with multi-byte character sets? — , May 03 '13 at 13:56
@a_horse_with_no_name Unless something changed very recently, MySQL's REGEXP operator is not multi byte safe. — Joachim Isaksson, May 03 '13 at 13:58

score 2 · Answer 1 · edited May 23 '17 at 12:18

2

MySQL's regexes don't work reliably with multibyte character sets.

However, if you're using UTF-8, you can instead make use of the accent insensitive LIKE and = searches which looks like it may be what you're really after anyway.

edited May 23 '17 at 12:18

Community

1
1

answered May 03 '13 at 13:52

Joachim Isaksson

176,943
25
281
294

score 0 · Answer 2 · answered May 03 '13 at 13:24

From the docs:

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

What you could try is handling the multibyte character like this so it is handling 1 byte or 2. It won't be perfect because it will matchin on "lion" for example, but may get you closer.

REGEXP 'l.{1,2}on'

Another thing that might be affecting you is the way that REGEXP works in MySQL is that there are implied wildcards on the end because you are not specifying the the beginning and end so

REGEXP 'l[éeè]on'

is equivalent to

REGEXP '^.*l[éeè]on.*$'

This means that you might be matching MORE than what you expect. Sounds like your problem is the first scenario I mention

REGEXP with MySQL isn't working properly

2 Answers2