Geek Answers Handbook

Mysql regex utf-8 characters

I am trying to get data from a MySQL via REGEX with or without special utf-8 characters.

Let me explain with an example:

If the user enters a word like sirena , he should return strings that include words like sirena , siréna , šíreňá .. and so on .. also he should work in the opposite direction, when he enters siréná , he should return the same results.

I am trying to search through REGEX , my query looks like this:

 SELECT * FROM `content` WHERE `text` REGEXP '[sšŠ][iíÍ][rŕŔřŘ][eéÉěĚ][nňŇ][AaáÁäÄ0]'

It works only when the word sirena is in the database, but not when there is the word siréňa ..

Is it because of something with UTF-8 and MySQL? (mysql utf8_general_ci column utf8_general_ci )

Thanks!

+7
regex mysql utf-8
source share
3 answers

MySQL regex library does not support utf-8.

See Error # 30241 Problems with regular expressions that have been open since 2007. They will have to change the regular expression library that they use before this can be fixed, and I did not find any messages about when or if they will do it.

The only workaround I've seen is to search for specific HEX strings:

 mysql> SELECT * FROM `content` WHERE HEX(`text`) REGEXP 'C3A9C588'; +----------+ | text | +----------+ | siréňa | +----------+

Your comment:

No, I do not know any solution with MySQL.

You may need to switch to PostgreSQL because this RDBMS supports \u codes for UTF characters in regular expression syntax .

+6
source share

Try something like ... REGEXP '(a|b|[ab])'

 SELECT * FROM `content` WHERE `text` REGEXP '(s|š|Š|[sšŠ])(i|í|Í|[iíÍ])(r|ŕ|Ŕ|ř|Ř|[rŕŔřŘ])(e|é|É|ě|Ě|[eéÉěĚ])(n|ň|Ň|[nňŇ])(A|a|á|Á|ä|Ä|0|[AaáÁäÄ0])'

It works for me!

0
source share

Use lib_mysqludf_preg library from mysql UDF repository for PCRE regular expressions directly in mysql

Although the MySQL regular expression library does not support utf-8, the mysql UDF repository has the ability to use the utf-8 compatible regular expression according to the PCRE regular expressions directly in mysql.

http://www.mysqludf.org/ https://github.com/mysqludf/lib_mysqludf_preg#readme

-3
source share

More articles:


All Articles