How the REGEXP_LIKE() Function Works in MySQL

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.

3 Ways to Detect if a String Matches a Regular Expression in MySQL

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:

• REGEXP_LIKE()
• REGEXP
• RLIKE

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.