MySQL has a number of functions and operators that allow us to perform operations using regular expressions (regex). This article presents two operators and one function that enable us to find out if a string matches a regular expression specified by a given pattern.
These regex functions and operators are:
These are all basically equivalent, as the operators (the second two) are both synonyms of the function (the first one). In any case, you can see examples of all three in action below.
The REGEXP_LIKE() Function
First, let’s look at the function. Here’s an example of running a regex search using the REGEXP_LIKE() function:
SELECT
REGEXP_LIKE('Car', '^C') AS 'Match',
REGEXP_LIKE('Bar', '^C') AS 'No Match';
Result:
+-------+----------+ | Match | No Match | +-------+----------+ | 1 | 0 | +-------+----------+
The first string matches (because it starts with C) so the result is 1. The second string doesn’t match and so the result is 0.
However, this function can be much more useful than simply returning a 1 or 0. For example, it can be added to the WHERE clause when querying a database. In this case, we can get a list of rows that contain a match for the pattern.
Here’s an example:
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 | +---------+--------------------------+
The REGEXP Operator
Here’s how to do the same thing using the REGEXP operator:
SELECT 'Car' REGEXP '^C' AS 'Match', 'Bar' REGEXP '^C' AS 'No Match';
Result:
+-------+----------+ | Match | No Match | +-------+----------+ | 1 | 0 | +-------+----------+
And the database example:
SELECT AlbumId, AlbumName FROM Albums WHERE AlbumName REGEXP '^Power';
Result:
+---------+------------+ | AlbumId | AlbumName | +---------+------------+ | 1 | Powerslave | | 2 | Powerage | +---------+------------+
You can also use NOT REGEXP to return the opposite result.
The RLIKE Operator
And here it is using RLIKE:
SELECT 'Car' RLIKE '^C' AS 'Match', 'Bar' RLIKE '^C' AS 'No Match';
Result:
+-------+----------+ | Match | No Match | +-------+----------+ | 1 | 0 | +-------+----------+
And the database example:
SELECT AlbumId, AlbumName FROM Albums WHERE AlbumName RLIKE '^Power';
Result:
+---------+------------+ | AlbumId | AlbumName | +---------+------------+ | 1 | Powerslave | | 2 | Powerage | +---------+------------+
In this case I simply swapped REGEXP for RLIKE and left the rest of the code alone.
You can also use NOT RLIKE to return the opposite result.
More REGEX Functions
MySQL also includes a few other regex functions and operators. Three of these are listed below. Technically, you could also use the first two to “detect” whether a string matches a regex pattern (in which case, maybe this article should be titled “5 Ways to Detect if a String Matches a Regular Expression in MySQL” instead of just “3 ways…”).
Anyway, here are three more regex functions:
- You can use the
REGEXP_INSTR()function to return the starting index of a substring that matches the regular expression pattern. - The
REGEXP_SUBSTR()function returns the substring that matches the given regular expression pattern. - And the
REGEXP_REPLACE()function replaces occurrences of the substring within a string that matches the given regular expression pattern.