MySQL’s support for regular expressions is rather limited, but still very useful. MySQL only has one operator that allows you to work with regular expressions. This is the REGEXP operator, which works just like the LIKE operator, except that instead of using the _ and % wildcards, it uses a POSIX Extended Regular Expression (ERE). Despite the “extended” in the name of the standard, the POSIX ERE flavor is a fairly basic regex flavor by modern standards. Still, it makes the REGEXP operator far more powerful and flexible than the simple LIKE operator.

One important difference between the LIKE and REGEXP operators is that the LIKE operator only returns True if the pattern matches the whole string. E.g. WHERE testcolumn LIKE 'jg' will return only rows where testcolumn is identical to jg, except for differences in case perhaps. On the other hand, WHERE testcolumn REGEXP 'jg' will return all rows where testcolumn has jg anywhere in the string. Use WHERE testcolumn REGEXP '^jg$' to get only columns identical to jg. The equivalent of WHERE testcolumn LIKE 'jg%' would be WHERE testcolumn REGEXP '^jg'. There’s no need to put a .* at the end of the regex (the REGEXP equivalent of LIKE’s %), since partial matches are accepted.

MySQL does not offer any matching modes. POSIX EREs don’t support mode modifiers inside the regular expression, and MySQL’s REGEXP operator does not provide a way to specify modes outside the regular expression. The dot matches all characters including newlines, and the caret and dollar only match at the very start and end of the string. In other words: MySQL treats newline characters like ordinary characters. The REGEXP operator applies regular expressions case insensitively if the collation of the table is case insensitive, which is the default. If you change the collation to be case sensitive, the REGEXP operator becomes case sensitive.

Remember that MySQL supports C-style escape sequences in strings. While POSIX ERE does not support tokens like \n to match non-printable characters like line breaks, MySQL does support this escape in its strings. So WHERE testcolumn REGEXP '\n' returns all rows where testcolumn contains a line break. MySQL converts the \n in the string into a single line break character before parsing the regular expression. This also means that backslashes need to be escaped. The regex \\ to match a single backslash becomes '\\\\' as a MySQL string, and the regex \$ to match a dollar symbol becomes '\\$' as a MySQL string. All this is unlike other databases like Oracle, which don’t support \n and don’t require backslashes to be escaped.

To return rows where the column doesn’t match the regular expression, use WHERE testcolumn NOT REGEXP 'pattern' The RLIKE operator is a synonym of the REGEXP operator. WHERE testcolumn RLIKE 'pattern' and WHERE testcolumn NOT RLIKE 'pattern' are identical to WHERE testcolumn REGEXP 'pattern' and WHERE testcolumn NOT REGEXP 'pattern'. I recommend you use REGEXP instead of RLIKE, to avoid confusion with the LIKE operator.

LIB_MYSQLUDF_PREG

If you want more regular expression power in your database, you can consider using LIB_MYSQLUDF_PREG. This is an open source library of MySQL user functions that imports the PCRE library. LIB_MYSQLUDF_PREG is delivered in source code form only. To use it, you’ll need to be able to compile it and install it into your MySQL server. Installing this library does not change MySQL’s built-in regex support in any way. It merely makes the following additional functions available:

PREG_CAPTURE extracts a regex match from a string. PREG_POSITION returns the position at which a regular expression matches a string. PREG_REPLACE performs a search-and-replace on a string. PREG_RLIKE tests whether a regex matches a string.

All these functions take a regular expression as their first parameter. This regular expression must be formatted like a Perl regular expression operator. E.g. to test if regex matches the subject case insensitively, you’d use the MySQL code PREG_RLIKE('/regex/i', subject). This is similar to PHP’s preg functions, which also require the extra // delimiters for regular expressions inside the PHP string.