MySQL | REGEXP_LIKE method
Start your free 7-days trial now!
MySQL's REGEXP_LIKE(~) method returns 1 if the input string matches the regular expression pat. If there is no match 0 is returned.
Parameters
1. expr | string
The string to check for matches with regular expression pattern.
2. pat | regular expression
The regular expression pattern for matching.
3. match_type | string | optional
Specifies how to perform matching. Multiple match types may be specified, however, if there is a contradiction between the provided match types the match type on the right take precedence.
match_type | Meaning |
|---|---|
| Case sensitive matching |
| Case-insensitive matching |
| 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. |
| The |
| Unix-only line endings. Only the newline character is recognized as a line ending by the |
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Return value
The return value depends on the following cases:
Case | Return value |
|---|---|
Input string matches the regular expression pattern | 1 |
Input string does NOT match the regular expression pattern | 0 |
Basic usage
To check whether student last names match the regular expression pattern '[ae]':
SELECT lname, REGEXP_LIKE(lname,'[ae]')FROM students;
+--------+---------------------------+| lname | REGEXP_LIKE(lname,'[ae]') |+--------+---------------------------+| Towner | 1 || Davis | 1 || Apple | 1 || David | 1 || Town | 0 |+--------+---------------------------+
Only 'Town' returns 0 as it does not contain an 'a' or an 'e'.
Match type parameter
To perform a case sensitive match by specifying match type 'c':
SELECT REGEXP_LIKE('ABC DEF GHI', '[a-z]+', 'c');
+-------------------------------------------+| REGEXP_LIKE('ABC DEF GHI', '[a-z]+', 'c') |+-------------------------------------------+| 0 |+-------------------------------------------+
The uppercase string 'ABC DEF GHI' does not match the regular expression '[a-z]+' as the match is case sensitive.
