CODEWITHSUNDEEP
mysqlsearchdiacritics
Philippe Carriere
Philippe Carriere

Reputation: 3720

Search for accentuated char in mysql

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.

Upvotes: 0

Views: 1122

Answers (1)

ax.
ax.

Reputation: 60217

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.

Upvotes: 1

Related Questions