Menu

MySQL REGEXP Operator

Updated on

MySQL REGEXP tests whether a string matches a regular expression. It is an operator, not a function call: write expr REGEXP pattern. RLIKE is a synonym. In MySQL 8.0.4 and later, both operators are synonyms for REGEXP_LIKE(). See the MySQL regular expression reference.

Syntax

expr REGEXP pattern

expr is the string to test. pattern is a regular expression.

Return value

REGEXP returns 1 for a match, 0 for no match, and NULL if either expr or pattern is NULL.

SELECT
    'Order 123' REGEXP '[0-9]+' AS contains_digits,
    '12345' REGEXP '^[0-9]+$' AS digits_only,
    '123a' REGEXP '^[0-9]+$' AS not_digits;

contains_digits  digits_only  not_digits
---------------  -----------  ----------
1                1            0

Without anchors, a pattern can match a substring. Use ^ and $ when the entire value must match.

Case sensitivity

Regular-expression comparisons use the character set and collation of the expression and pattern. The default may therefore be case-sensitive or case-insensitive depending on those collations. Use REGEXP_LIKE() with match_type c or i when you need to specify case behavior explicitly.

MySQL 8.0.4 replaced the earlier regular-expression engine with ICU, which supports Unicode and multibyte strings. Patterns written for older versions may need compatibility review; see the MySQL 8.0.4 release notes.