MySQL REGEXP_LIKE() Function

The MySQL REGEXP_LIKE() function is used to perform regular expression matching. It returns 1 if the string matches the regular expression specified by pattern, else returns 0. If the string or pattern is NULL, the return value will be NULL.

By default, regular expression operations use the character set and collation of the string and pattern arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply. Arguments may be specified with explicit collation indicators to change comparison behavior.

Syntax

REGEXP_LIKE(string, pattern, match_type)

Parameters

`string`	`Required.` Specify the string to search.
`pattern`	`Required.` Specify the regular expression matching information. See the table below for pattern syntax.
`match_type`	`Optional.` Specify how to perform matching. It can take following values: c - Case-sensitive matching i - Case-insensitive matching 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 n - The . character matches line terminators. The default is for . matching to stop at the end of a line u - Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators

Regular Expression Patterns

Symbol	Description
^	Matches the beginning of a string.
$	Matches the end of a string.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
\|	Used like an "OR" to specify more than one alternative.
[ ]	Matches any single character specified within [].
[^ ]	Matches any single character that is not specified within [].
-	Represents a range of characters.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.
[..]	Matches one collation element that can be more than one character.
[=character_class=]	Represents an equivalence class. It matches all characters with the same collation value, including itself.
[:character_class:]	Represents a character class that matches all characters belonging to that class.

List of standard class name

Character Class Name	Meaning
alnum	Alphanumeric characters
alpha	Alphabetic characters
blank	Whitespace characters
cntrl	Control characters
digit	Digit characters
graph	Graphic characters
lower	Lowercase alphabetic characters
print	Graphic or space characters
punct	Punctuation characters
space	Space, tab, newline, and carriage return
upper	Uppercase alphabetic characters
xdigit	Hexadecimal digit characters

Example: REGEXP_LIKE() examples

The example below shows the usage of REGEXP_LIKE() function.

mysql> SELECT REGEXP_LIKE('dog cat dog', 'dog');
Result: 1

mysql> SELECT REGEXP_LIKE('aa aaa aaaa', 'a{4}');
Result: 1

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
Result: 1

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
Result: 0

Example: Case-sensitive search

The match_type parameter can be specified as c to perform case sensitive search. If either argument is a binary string, the arguments are handled in case-sensitive fashion, even if match_type contains the i character. See the example below:

mysql> SELECT REGEXP_LIKE('XYZ','y');
Result: 1

/* case-sensitive search */
mysql> SELECT REGEXP_LIKE('XYZ', 'y', 'c');
Result: 0

/* case-insensitive search */
mysql> SELECT REGEXP_LIKE('XYZ', 'y', 'i');
Result: 1

/* using (?-i) pattern for case-sensitive search */
mysql> SELECT REGEXP_LIKE('XYZ','(?-i)y');
Result: 0

Example: Match on more than one alternative

Consider a table called Employee. The below query is used to fetch all records from this table where Name contains either "Ja", "Je" or "Ji". To specify more than one alternative for the second character of the pattern, | is used.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Name, 'J(a|e|i)');

Example: Match on Beginning

Consider a table called Employee. The below query is used to fetch all records from this table where Name starts with "J". Here, ^ is used to specify starting character of the pattern.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Name, '^J');

Example: Match on End

Consider a table called Employee. The below query is used to fetch all records from this table where Name ends with "n". Here, $ is used to specify end character of the pattern.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Name, 'n$');

Example: Matching List

Consider a table called Employee. The below query is used to fetch all records from this table where Name contains either "Ja", "Jb", "Jc" or "Jd". To specify the second character of the pattern, [ ] is used, which can be either "a", "b", "c" or "d" in this case.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Name, 'J[a-d]');

Example: Non-Matching List

Consider a table called Employee. The below query is used to fetch all records from this table where Name does not contain either "Ja", "Jb", "Jc" or "Jd". The [ ^ ] pattern is used to specify characters which are not listed here.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Name, 'J[^a-d]');

Example: Match Character Class

Consider a table called Employee. The below query is used to fetch all records from this table where Address contains a pattern which starts with 123 and then the remainder of the characters are alphanumeric characters.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Address, '123[[:alnum:]]*');

Example: Escaping Characters

To escape special characters when using the REGEXP function, the special character should be preceded with two backslashes \\. Consider a table called Employee. The below query can be used to fetch all records of this table where Address contains a ( character.

SELECT * FROM Employee
WHERE REGEXP_LIKE(Address, '\\(');

❮ MySQL Functions