MySQL REGEXP query - accent insensitive search

Question

I'm looking to query a database of wine names, many of which contain accents (but not in a uniform way, and so similar wines may be entered with or without accents)

The basic query looks like this:

SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugères[[:>:]]'

which will return entries with 'Faugères' in the title, but not 'Faugeres'

SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugeres[[:>:]]'

does the opposite.

I had thought something like:

SELECT * 
FROM `table` 
WHERE `wine_name` REGEXP '[[:<:]]Faug[eèêéë]r[eèêéë]s[[:>:]]'

might do the trick, but this only returns the results without the accents.

The field is collated as utf8_unicode_ci, which from what I've read is how it should be.

Any suggestions?!

Fernandafernande

I had the same problem. Look at my topic here: #33722636 – Entwistle

Answer 1

You're out of luck:

Warning

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.

The [[:<:]] and [[:>:]] regexp operators are markers for word boundaries. The closest you can achieve with the LIKE operator is something on this line:

SELECT *
FROM `table`
WHERE wine_name = 'Faugères'
   OR wine_name LIKE 'Faugères %'
   OR wine_name LIKE '% Faugères'

As you can see it's not fully equivalent because I've restricted the concept of word boundary to spaces. Adding more clauses for other boundaries would be a mess.

You could also use full text searches (although it isn't the same) but you can't define full text indexes in InnoDB tables (yet).

You're certainly out of luck :)

---

Addendum: this has changed as of MySQL 8.0:

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe.

Ocher

Ouch... - OK, so if I switch to: WHERE wine_name LIKE '%Faugeres%' is there any downside? I can't remember why we were using REGEXP to begin with, but I think it was to do with searching whole words and not strings within words, which the above like statement would do... – Fernandafernande

This solution may not be so good because it won't work if the word has other characters after or before it, like: ´Faugères.´ ´Faugères!´ ´Faugères?´ ´(Faugères´ and many many other variations. I'm looking for the samething: REGEXP to use word boundaries but accent-insensitive. – Thanos

Still no luck with mysql 8. REGEXP '\\bFaugeres\\b' didn't work. I mean the accent insensitive doesn't work. – Improve

@Improve MySQL 8 fixes multi-byte support. Regular expressions aren't expected to handle collation that way: è and e are different characters per se. You may want to try '\\bFaug[eèêéë]r[eèêéë]s\\b'. – Burdette

Answer 2

Because REGEXP and RLIKE are byte oriented, have you tried:

SELECT 'Faugères' REGEXP 'Faug(e|è|ê|é|ë)r(e|è|ê|é|ë)s';

This says one of these has to be in the expression. Notice that I haven't used the plus(+) because that means ONE OR MORE. Since you only want one you should not use the plus.

Renfrew

This should be the answer. – Improve

Answer 3

utf8_general_ci see no difference between accent/no accent when sorting. Maybe this true for searches as well. Also, change REGEXP to LIKE. REGEXP makes binary comparison.

Irma

Answer 4

To solve this problem, I tried different things, including using the binary keyword or the latin1 character set but to no avail.
Finally, considering that it is a MySql bug, I ended up replacing the é and è chars,

Like this :

SELECT * 
FROM `table` 
WHERE replace(replace(wine_name, 'é', 'e'), 'è', 'e') REGEXP '[[:<:]]Faugeres[[:>:]]'

Mutt

This may be inefficient, because it calculates the expression for each row of the table. It can apply the condition only after reading the whole table. – Hairworm

Answer 5

I had the same problem trying to find every record matching one of the following patterns: 'copropriété', 'copropriete', 'COPROPRIÉTÉ', 'Copropri?t?'

REGEXP 'copropri.{1,2}t.{1,2} worked for me. Basically, .{1,2} will should work in every case wether the character is 1 or 2 byte encoded.

Explanation: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Warning
The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte 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.

Farrar

Answer 6

I have this problem, and went for Álvaro's suggestion above. But in my case, it misses those instances where the search term is the middle word in the string. I went for the equivalent of:

SELECT *
FROM `table`
WHERE wine_name = 'Faugères'
   OR wine_name LIKE 'Faugères %'
   OR wine_name LIKE '% Faugères'
   OR wine_name LIKE '% Faugères %'

Beatify

Answer 7

Ok I just stumbled on this question while searching for something else.

This returns true.

SELECT 'Faugères' REGEXP 'Faug[eèêéë]+r[eèêéë]+s';

Hope it helps.

Adding the '+' Tells the regexp to look for one or more occurrences of the characters.

Otherness