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.