Menu

MySQL REGEXP_LIKE() Function

Updated on

MySQL REGEXP_LIKE() tests whether a string matches a regular expression. It is available in MySQL 8.0.4 and later. The REGEXP and RLIKE operators provide equivalent pattern tests. See the MySQL regular expression reference.

Syntax

REGEXP_LIKE(expr, pattern [, match_type])
  • expr: The string to test.
  • pattern: The regular expression.
  • match_type: Optional flags that control how matching works.

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

Match type

Use c to request case-sensitive matching and i to request case-insensitive matching. Other flags control multiline and newline behavior: m enables multiline anchors, n lets . match line terminators, and u restricts line endings to Unix-style \n. If flags conflict, the rightmost one takes precedence.

SELECT
    REGEXP_LIKE('hello', '^[a-z]+$', 'c') AS lowercase,
    REGEXP_LIKE('hello', '^[A-Z]+$', 'i') AS ignore_case,
    REGEXP_LIKE('12345', '^[0-9]+$', 'c') AS digits,
    REGEXP_LIKE('123ab', '^[0-9]+$', 'c') AS not_digits;

lowercase  ignore_case  digits  not_digits
---------  -----------  ------  ----------
1          1            1       0

Without an explicit c or i flag, MySQL uses the character set and collation of the arguments to determine comparison behavior. A common case-insensitive collation makes letter-case differences match, but other collations can be case-sensitive.

MySQL 8.0.4 changed regular-expression support from the older Henry Spencer implementation to ICU. Review the 8.0.4 release notes when upgrading an application that depends on older regular-expression behavior.