MySQL REGEXP and REGEXP_LIKE(): Pattern Matching Examples

MySQL provides the REGEXP and RLIKE operators and the REGEXP_LIKE() function to test a string against a regular expression. REGEXP and RLIKE are operator forms of REGEXP_LIKE(); REGEXP() is not the function-call syntax. The REGEXP_LIKE() function is available in MySQL 8.0.4 and later; use REGEXP or RLIKE on older versions. See the MySQL regular expression reference and SQLiz references for REGEXP and RLIKE.

Match a substring

REGEXP matches a pattern anywhere in the expression unless the pattern uses anchors. This query returns 1 because the text contains one or more digits:

SELECT 'Order 123' REGEXP '[0-9]+' AS has_number;

has_number
----------
1

RLIKE is a synonym for REGEXP:

SELECT 'Order 123' RLIKE '[0-9]+' AS has_number;

For the function form, use REGEXP_LIKE():

SELECT REGEXP_LIKE('Order 123', '[0-9]+') AS has_number;

All three forms return 1 for a match, 0 for no match, or NULL if either the string or pattern is NULL.

Match the entire string

Use ^ for the start and $ for the end of the string. For example, this pattern accepts a string made entirely of digits:

SELECT '12345' REGEXP '^[0-9]+$' AS digits_only;

digits_only
-----------
1

Without both anchors, a pattern such as [0-9]+ matches a digit sequence inside longer text as well.

Control case sensitivity

Regular expression matching normally follows the character set and collation of the string and pattern. REGEXP_LIKE() also accepts a match_type argument: c requests case-sensitive matching and i requests case-insensitive matching.

SELECT
    REGEXP_LIKE('Hello', '^hello$', 'c') AS case_sensitive,
    REGEXP_LIKE('Hello', '^hello$', 'i') AS case_insensitive;

case_sensitive  case_insensitive
--------------  ----------------
0               1

See SQLiz’s REGEXP_LIKE() reference for the available match-control characters and examples.

Use regular expressions with other operations

Use REGEXP_INSTR() to find a matching substring’s position, REGEXP_SUBSTR() to return the matching text, and REGEXP_REPLACE() to replace matches. See the SQLiz references for REGEXP_INSTR(), REGEXP_SUBSTR(), and REGEXP_REPLACE().

MySQL version and string escaping

MySQL 8.0.4 replaced its earlier regular expression implementation with ICU, which supports Unicode and multibyte strings. Older MySQL versions used a different engine, so review patterns when upgrading from a version before 8.0.4. See the MySQL 8.0.4 release notes.

MySQL string literals interpret backslash escapes. If a pattern contains a backslash escape such as \d, write the backslash twice in the SQL string ('\\d') unless NO_BACKSLASH_ESCAPES is enabled. For simple ASCII digits, [0-9] avoids that extra escaping.