regex in mySQL and MariaDB give different results

user_14735461 0
Viewed 273

I am migrating my MariaDB to MySQL and have come across differences.

I have a very simple query that produces results (197) in Maria DB but Zero in mySQL can anyone help?

SELECT DISTINCT title FROM films where title REGEXP 'The \\w{4}[^\\s]*\\b'

The database is exactly the same (exported from MariaDB into MySQL with no issues).

mariadb compatibility mysql regex
1 Answers

In MySQL 5.7, you have to use the POSIX-like regex library and use

SELECT DISTINCT title FROM films where title REGEXP 'The [[:alnum:]_]{4}[^[:space:]]*[[:>:]]'

Also, note that the regex matching here will be case insensitive, if you need to make The only match The and not THE, you need to add the BINARY keyword after REGEXP.

Here,

  • [[:alnum:]_]{4} - \w{4} - four word chars, letters, digits or underscores
  • [^[:space:]]* - \S* - zero or more non-whitespace chars
  • [[:>:]] - \b(?!\w) - a right-hand (trailing) word boundary
user_38329700
Related