In MySQL, the REGEXP_LIKE() function is used to determine whether or not a string matches a regular expression.
The function returns 1 if the string matches the regular expression provided, and 0 if it doesn’t.
Syntax
The syntax goes like this:
REGEXP_LIKE(expr, pat[, match_type])
Where expr is the input string and pat is the regular expression for which you’re testing the string against.
The optional match_type argument allows you to refine the regular expression. For example, you can use match_type to specify case-sensitive matching or not.
Example 1 – Basic Usage
Here’s a basic example:
SELECT REGEXP_LIKE('Cat', '.*') Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
In this case, our regular expression specifies any character in any sequence, so of course we get a match. The function returns 1 to indicate a match.
Example 2 – No Match
Here’s an example where the input string doesn’t match the regular expression:
SELECT REGEXP_LIKE('Cat', 'b+') Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
In this case, our regular expression specifies that there should be one or more b characters in any sequence. Our input string doesn’t contain this character and so 0 is returned.
Example 3 – Match the Beginning of a String
Here’s an example where the regular expression specifies that the string must begin with certain characters:
SELECT REGEXP_LIKE('Cat', '^Ca') Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
And here’s what happens if there’s no match:
SELECT REGEXP_LIKE('Cat', '^Da') Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
Example 4 – A Database Query
This function can be used in the WHERE clause of database queries to return only those rows that contain the pattern:
SELECT AlbumId, AlbumName FROM Albums WHERE REGEXP_LIKE(AlbumName, '^Power');
Result:
+---------+------------+ | AlbumId | AlbumName | +---------+------------+ | 1 | Powerslave | | 2 | Powerage | +---------+------------+
Here’s the full table:
SELECT AlbumId, AlbumName FROM Albums;
Result:
+---------+--------------------------+ | AlbumId | AlbumName | +---------+--------------------------+ | 1 | Powerslave | | 2 | Powerage | | 3 | Singing Down the Lane | | 4 | Ziltoid the Omniscient | | 5 | Casualties of Cool | | 6 | Epicloud | | 7 | Somewhere in Time | | 8 | Piece of Mind | | 9 | Killers | | 10 | No Prayer for the Dying | | 11 | No Sound Without Silence | | 12 | Big Swing Face | | 13 | Blue Night | | 14 | Eternity | | 15 | Scandinavia | | 16 | Long Lost Suitcase | | 17 | Praise and Blame | | 18 | Along Came Jones | | 19 | All Night Wrong | | 20 | The Sixteen Men of Tain | +---------+--------------------------+
Example 5 – The match_type Argument
You can provide an additional argument to determine the match type. This allows you to specify things like whether or not the match is case-sensitive, whether or not to include line terminators, etc.
Here’s an example of specifying a case-sensitive match and a case-insensitive match:
SELECT
REGEXP_LIKE('Cat', '^ca', 'c') 'Case-Sensitive',
REGEXP_LIKE('Cat', '^ca', 'i') 'Case-Insensitive';
Result:
+----------------+------------------+ | Case-Sensitive | Case-Insensitive | +----------------+------------------+ | 0 | 1 | +----------------+------------------+
The match_type argument can contain the following characters:
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.
More Examples
You can see more examples of basic regular expressions at MySQL REGEXP Examples. REGEXP is a synonym for REGEXP_LIKE(), so you can use the examples interchangeably.
Also see Regular Expression Syntax from the MySQL documentation.