The regular expression flavor used by the REGEXP operator depends on your version of MySQL. To find out, execute SELECT ICU_VERSION() to determine which version of ICU your MySQL server was compiled with. If this returns a number then the REGEXP operator uses the regex engine provided by that version of ICU. ICU provides a very feature-rich regex syntax. If MySQL says the function does not exist then you are using MySQL 5.x which uses POSIX ERE as its regex flavor. This is a limited regex flavor by modern standards. Still, it makes the REGEXP operator far more powerful and flexible than the simple LIKE operator.

The regular expressions tutorial and regular expressions reference on this website do not mention MySQL specifically. They do cover POSIX ERE and ICU in detail, and mention specific versions of ICU as needed. That is the information you need to use with MySQL.

REGEXP Operator

MySQL 5.x 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 ERE. MySQL 8.x and 9.x still support this operator, but now use ICU regular expressions.

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.

The REGEXP operator does not provide a way to set any matching modes outside the regular expression. The REGEXP operator applies regular expressions case insensitively if the collation of the column that the operator is applied to is case insensitive, which is the default. If you change the column’s collation to be case sensitive then the REGEXP operator becomes case sensitive. This applies to MySQL 5.x as well as 8.x and later.

In MySQL 5.x, the POSIX ERE syntax does not provide mode modifiers either. 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 5.x treats newline characters like ordinary characters.

In MySQL 8.x and later, the ICU syntax does provide mode modifiers. Importantly, by default the ICU regex engine does not allow the dot to match any characters that Unicode treats as line breaks. Start your regex with (?s) if you need the dot to match line breaks.

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.

The ICU regex flavor does support C-style escape sequences. So interprets the regex \n as a newline. This means WHERE testcolumn REGEXP '\\n' matches a newline in MySQL 8.x while it matched \n literally in MySQL 5.x. WHERE testcolumn REGEXP '\\\\n' matches \n literally with any version of MySQL as the regex \\n does so with both POSIX ERE and ICU.

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.

MySQL 8 Adds 4 REGEXP Functions

In addition to switching the regex engine to ICU, MySQL 8 also adds 4 REGEXP functions. These are very similar to the functions available in Oracle. But remember that the regex flavors are different.

REGEXP_LIKE(source, regexp[, modes]) does the same as the REGEXP operator, returning 1 or 0 depending on whether or not the regex can find a match in the subject. It accepts an optional 3rd parameter that lets you set matching modes outside the regular expression.

• 'i': Turn on case insensitive matching. The default depends on the column’s collation.
• 'c': Turn on case sensitive matching. The default depends on the column’s collation.
• 'n': Make the dot match any character, including line breaks. By default, the dot matches any character except characters treated as line breaks by Unicode.
• 'm': Make the caret and dollar match at the start and end of each line, recognizing all Unicode line breaks, and treating a CRLF pair as a single line break. By default, these only match at the very start and the very end of the string.
• 'u': UNIX-only line breaks. When the dot doesn’t match line breaks or when the caret and dollar match at the start and end of each line then recognize only the newline character as a line break.

Unlike Oracle, MySQL does not support the 'x' flag to enable free-spacing. ICU does support that with the mode modifier (?x).

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) returns a string with the part of source matched by the regular expression. If the match attempt fails then NULL is returned. You can use REGEXP_SUBSTR with a single string or with a column. You can use it in SELECT clauses to retrieve only a certain part of a column. The position parameter specifies the character position in the source string at which the match attempt should start. The first character has position 1. The occurrence parameter specifies which match to get. Set it to 1 to get the first match. If you specify a higher number then MySQL will continue to attempt to match the regex starting at the end of the previous match, until it found as many matches as you specified. The last match is then returned. If there are fewer matches then NULL is returned. Do not confuse this parameter with backreferences. MySQL does not provide a function to return the part of the string matched by a capturing group. The last three parameters are optional.

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) returns the source string with one or all regex matches replaced. If no matches can be found, the original string is replaced. If you specify a positive number for occurrence (see the above paragraph) only that match is replaced. If you specify zero or omit the parameter, all matches are replaced. The last three parameters are optional. The replacement parameter is a string that each regex match will be replaced with. The replacement string syntax is that provided by ICU, which is completely different to that provided by Oracle. You can use the $0 to insert the whole regex match, $1 and so on for numbered backreferences, and ${name} for named backreferences. A backslash in the replacement escapes the next character. To replace with a literal backslash, use the replacement \\. Since MySQL requires backslashes in string literals to be escaped you need to code this replacement as the string '\\\\'.

REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes) returns the beginning or ending position of a regex match in the source string. This function takes the same parameters as REGEXP_SUBSTR, plus one more. Set return_option to zero or omit the parameter to get the position of the first character in match. Set it to one to get the position of the first character after the match. The first character in the string has position 1. REGEXP_INSTR returns zero if the match cannot be found. The last 4 parameters are optional.

Ad-Free Access and Printable PDF Download

If you find the content on this website helpful they you may want a copy you can read offline or even print, or browse the site as often as you want without ads. You can purchase your own copy of the Regular-Expressions.info printable PDF download. As a bonus, you'll get a lifetime of advertisement-free access to this site!