top of page
CerebroSQL

MySQL: 

REGEXP_LIKE

REGEXP_LIKE(expr, pat[, match_type])

Returns 1 if the string expr matches the regular expression specified
by the pattern pat, 0 otherwise. If expr or pat is NULL, the return
value is NULL.

The pattern can be an extended regular expression, the syntax for which
is discussed in
https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax. The
pattern need not be a literal string. For example, it can be specified
as a string expression or table column.

The optional match_type argument is a string that may contain any or
all the following characters specifying how to perform matching:

o c: Case-sensitive matching.

o i: Case-insensitive matching.

o m: Multiple-line mode. Recognize line terminators within the string.
The default behavior is to match line terminators only at the start
and end of the string expression.

o n: The . character matches line terminators. The default is for .
matching to stop at the end of a line.

o u: Unix-only line endings. Only the newline character is recognized
as a line ending by the ., ^, and $ match operators.

If characters specifying contradictory options are specified within
match_type, the rightmost one takes precedence.

By default, regular expression operations use the character set and
collation of the expr and pat arguments when deciding the type of a
character and performing the comparison. If the arguments have
different character sets or collations, coercibility rules apply as
described in
https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.
html. Arguments may be specified with explicit collation indicators to
change comparison behavior.

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
+---------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
+---------------------------------------+
| 1 |
+---------------------------------------+
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
+------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------+

match_type may be specified with the c or i characters to override the
default case sensitivity. Exception: If either argument is a binary
string, the arguments are handled in case-sensitive fashion as binary
strings, even if match_type contains the i character.

*Note*:

Because MySQL uses the C escape syntax in strings (for example, \n to
represent the newline character), you must double any \ that you use in
your expr and pat arguments.

URL: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Example

mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
| 1 |
+----------------------------+
mysql> SELECT REGEXP_LIKE('a', 'A'), REGEXP_LIKE('a', BINARY 'A');
+-----------------------+------------------------------+
| REGEXP_LIKE('a', 'A') | REGEXP_LIKE('a', BINARY 'A') |
+-----------------------+------------------------------+
| 1 | 0 |
+-----------------------+------------------------------+

mysql> SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
| 1 |
+---------------------------+
mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
| 0 |
+--------------------------------+

bottom of page