MySQL REGEXP_LIKE() Function

In MySQL, The REGEXP_LIKE() function checks whether a string matches a regular expression.

REGEXP and RLIKE operators are equivalent to REGEXP_LIKE() function.

By default, The REGEXP_LIKE() function performs case-insensitive matching.

REGEXP_LIKE() Syntax

here is the syntax of MySQL REGEXP_LIKE() function:

REGEXP_LIKE(str, regexp)
REGEXP_LIKE(str, regexp, mode)

Parameters

str

Required. The string to be tested.

regexp

Required. The regular expression to which the string str is to be matched.

mode

Optional. The match mode. It indicates how to perform matching.

mode can be one or more values in the followings:

• c: Case-sensitive
• i: Case-insensitive
• m: Multi-line match pattern
• n: The dot . can match end of line
• u: Unix line endings only

If there are conflicting options in mode, the rightmost one takes precedence.

Return value

If the string str matchs the regular expression regexp, REGEXP_LIKE() returns 1,otherwise it return 0.

if str or regexp is NULL, REGEXP_LIKE() will return NULL.

REGEXP_LIKE() Examples

Here are a few common Examples of REGEXP_LIKE().

SELECT
    REGEXP_LIKE('hello', '^[a-z]+$'),
    REGEXP_LIKE('hello', '^[A-Z]+$'),
    REGEXP_LIKE('12345', '[0-9]+$'),
    REGEXP_LIKE('12345', '^\\d+$'),
    REGEXP_LIKE('123ab', '^\\d*$'),
    REGEXP_LIKE('123ab', '^.*$')\G

     REGEXP_LIKE('hello', '^[a-z]+$'): 1
     REGEXP_LIKE('hello', '^[A-Z]+$'): 1
      REGEXP_LIKE('12345', '[0-9]+$'): 1
       REGEXP_LIKE('12345', '^\\d+$'): 1
       REGEXP_LIKE('123ab', '^\\d*$'): 0
         REGEXP_LIKE('123ab', '^.*$'): 1

Case sensitive

You can set the match mode to 'c' to enable case-sensitivity matching.

SELECT REGEXP_LIKE('hello', '^[A-Z]+$', 'c');

+---------------------------------------+
| REGEXP_LIKE('hello', '^[A-Z]+$', 'c') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+