Search code examples
mysqlregexmariadbcompatibility

regex in mySQL and MariaDB give different results

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).

Solution

  • 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