How the REGEX_REPLACE() Function Works in MySQL

In MySQL, the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern.

The whole string is returned along with the replacements.
If there’s no match (i.e. the input string doesn’t contain the substring), the the whole string is returned unchanged.

Syntax

The syntax goes like this:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Where expr is the input string and pat is the regular expression pattern for the substring. The repl argument is the replacement string.
The optional pos argument allows you to specify a position within the string to start the search. If omitted, it starts at position 1.
The optional occurrence argument allows you to specify which occurrence of the match to search for. If omitted, all occurrences are replaced.
The optional match_type argument allows you to refine the regular expression. For example, you can use this argument to specify case-sensitive matching or not.

Example 1 – Basic Usage

Here’s a basic example:

SET @str = 'It was good';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'good', 'great!') 'Result';

Result:

+-----------------+---------------+
| Original String | Result        |
+-----------------+---------------+
| It was good     | It was great! |
+-----------------+---------------+

In this case there’s a match, and the string is returned with the modification.

Example 2 – Multiple Matches

By default, if there are multiple matches within the string, all of them are replaced:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cat', 'Tiger') 'Result';

Result:

+---------------------+---------------------------+
| Original String     | Result                    |
+---------------------+---------------------------+
| Cat Dog Cat Dog Cat | Tiger Dog Tiger Dog Tiger |
+---------------------+---------------------------+

However, you also have the option of specifying which occurrence you’d like to replace (more on this later).

Example 3 – No Match

Here’s an example where there’s no match:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cow', 'Tiger') 'Result';

Result:

+---------------------+---------------------+
| Original String     | Result              |
+---------------------+---------------------+
| Cat Dog Cat Dog Cat | Cat Dog Cat Dog Cat |
+---------------------+---------------------+

There’s no match, so the string is returned unchanged.

Example 4 – The pos Argument

Here’s an example of specifying the starting position:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cat', 'Tiger', 2) 'Result';

Result:

+---------------------+-------------------------+
| Original String     | Result                  |
+---------------------+-------------------------+
| Cat Dog Cat Dog Cat | Cat Dog Tiger Dog Tiger |
+---------------------+-------------------------+

We started at position 2, which comes after the start of the first occurrence, so the replace operation only affects those occurrences that come after the first one.

Example 5 – The occurrence Argument

As mentioned, by default, all occurrences are replaced. However, you also have the option of specifying a specific occurrence to replace by using the occurrence argument. Here’s an example:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cat', 'Tiger', 1, 2) 'Result';

Result:

+---------------------+-----------------------+
| Original String     | Result                |
+---------------------+-----------------------+
| Cat Dog Cat Dog Cat | Cat Dog Tiger Dog Cat |
+---------------------+-----------------------+

In this case we start at position 1. However, if we start at a different position, the result is different:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cat', 'Tiger', 2, 2) 'Result';

Result:

+---------------------+-----------------------+
| Original String     | Result                |
+---------------------+-----------------------+
| Cat Dog Cat Dog Cat | Cat Dog Cat Dog Tiger |
+---------------------+-----------------------+

This happened because our starting position came after the first occurrence had started. Therefore, occurrence 2 became occurrence 1, and occurrence 3 became occurrence 2.
The default value for the occurrence argument is 0, which means all occurrences are replaced. In other words, if you omit this argument, all occurrences are replaced (as we’ve seen in the previous examples). Here’s an example of explicitly specifying all occurrences:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'Cat', 'Tiger', 1, 0) 'Result';

Result:

+---------------------+---------------------------+
| Original String     | Result                    |
+---------------------+---------------------------+
| Cat Dog Cat Dog Cat | Tiger Dog Tiger Dog Tiger |
+---------------------+---------------------------+

Example 6 – 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:

SET @str = 'Cat Dog Cat Dog Cat';
SELECT 
  @str 'Original String',
  REGEXP_REPLACE(@str, 'cat', 'Tiger', 1, 0, 'c') 'Case-Sensitive',
  REGEXP_REPLACE(@str, 'cat', 'Tiger', 1, 0, 'i') 'Case-Insensitive';

Result:

+---------------------+---------------------+---------------------------+
| Original String     | Case-Sensitive      | Case-Insensitive          |
+---------------------+---------------------+---------------------------+
| Cat Dog Cat Dog Cat | Cat Dog Cat Dog Cat | Tiger Dog Tiger Dog Tiger |
+---------------------+---------------------+---------------------------+

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.

How the REGEXP_INSTR() Function Works in MySQL

In MySQL, the REGEXP_INSTR() function returns the starting index of a substring that matches the regular expression pattern.

The index starts at 1. If there’s no match, the result is 0.

Syntax

The syntax goes like this:

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

Where expr is the input string and pat is the regular expression pattern for the substring.
The optional pos argument allows you to specify a position within the string to start the search. If omitted, it starts at position 1.
The optional occurrence argument allows you to specify which occurrence of the match to search for. If omitted, the first occurrence is used (occurrence 1).
The optional return_option argument allows you to specify which type of position to return. If you use 0, it will return the position of the first character in the matching substring. If you use 1 it returns the position of the first character following the matching substring. If omitted, the value is 0.
The optional match_type argument allows you to refine the regular expression. For example, you can use this argument to specify case-sensitive matching or not.

Example 1 – Basic Usage

Here’s a basic example:

SELECT REGEXP_INSTR('Cat', 'at') Result;

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+

In this case there’s a match, and the substring starts at position 2.

Example 2 – No Match

Here’s an example where there’s no match:

SELECT REGEXP_INSTR('Cat', '^at') Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

There’s no match, so the result is 0. There’s no match because I specified that the string must start with the substring.
Let’s change it so that it does start with that substring:

SELECT REGEXP_INSTR('at', '^at') Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Example 3 – The pos Argument

Here’s an example of specifying a starting position:

SELECT REGEXP_INSTR('Cat Cat', 'Cat', 2) Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

So we get the index of the second occurrence.
Note that the index still starts counting from position 1 regardless of where you specify the starting position.
The following example demonstrates this more clearly:

SELECT 
  REGEXP_INSTR('Cat Cat', 'Cat', 2) AS 'Pos 2', 
  REGEXP_INSTR('Cat Cat', 'Cat', 3) AS 'Pos 3', 
  REGEXP_INSTR('Cat Cat', 'Cat', 5) AS 'Pos 5';

Result:

+-------+-------+-------+
| Pos 2 | Pos 3 | Pos 5 |
+-------+-------+-------+
|     5 |     5 |     5 |
+-------+-------+-------+

Of course, depending on your regex pattern, this can return the index of completely different substrings. Example:

SELECT 
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';

Result:

+-------+-------+-------+
| Pos 1 | Pos 2 | Pos 6 |
+-------+-------+-------+
|     1 |     5 |    16 |
+-------+-------+-------+

We can check the substrings with the REGEXP_SUBSTR() function:

SELECT 
  REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 1) 'Pos 1',
  REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 2) 'Pos 2',
  REGEXP_SUBSTR('Cat City is SO Cute!', 'C.t', 6) 'Pos 6';

Result:

+-------+-------+-------+
| Pos 1 | Pos 2 | Pos 6 |
+-------+-------+-------+
| Cat   | Cit   | Cut   |
+-------+-------+-------+

Example 4 – The occurrence Argument

Here’s an example of using the occurrence argument. In all cases, we start at position 1:

SELECT 
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1) 'Occurrence 1',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2) 'Occurrence 2',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3) 'Occurrence 3';

Result:

+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
|            1 |            5 |           16 |
+--------------+--------------+--------------+

However, if we start at a different position, the result is different:

SELECT 
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 1) 'Occurrence 1',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 2) 'Occurrence 2',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 2, 3) 'Occurrence 3';

Result:

+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
|            5 |           16 |            0 |
+--------------+--------------+--------------+

This happened because our starting position came after the first occurrence had started. Therefore, occurrence 2 became occurrence 1, and occurrence 3 became occurrence 2. And because there were no more occurrences, the result of occurrence 3 was negative (i.e. there was no occurrence 3).

Example 5 – The return_option Argument

Here’s an example of using the return_option argument:

SELECT 
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Option 0',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1) 'Option 1';

Result:

+----------+----------+
| Option 0 | Option 1 |
+----------+----------+
|        1 |        4 |
+----------+----------+

Option 0 returned the matched substring’s first character. Option 1 returned the position following the matched substring.
Here’s what it looks like if we apply it to the previous example:

SELECT 
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 0) 'Occurrence 1',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 0) 'Occurrence 2',
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 0) 'Occurrence 3'
UNION ALL
SELECT
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 1, 1),
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 2, 1),
  REGEXP_INSTR('Cat City is SO Cute!', 'C.t', 1, 3, 1);

Result:

+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
|            1 |            5 |           16 |
|            4 |            8 |           19 |
+--------------+--------------+--------------+

In this case we did one set using option 0, and another using option 1, then joined them together using UNION ALL.

Example 6 – 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_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, 'c') 'Case-Sensitive',
  REGEXP_INSTR('Cat City is SO Cute!', 'c.t', 1, 1, 0, '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.

How the REGEXP_SUBSTR() Function Works in MySQL

In MySQL, the REGEXP_SUBSTR() function returns the substring that matches the given regular expression pattern.

If there’s no match (i.e. the input string doesn’t contain the substring), the result is NULL.

Syntax

The syntax goes like this:

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

Where expr is the input string and pat is the regular expression pattern for the substring.

The optional pos argument allows you to specify a position within the string to start the search. If omitted, it starts at position 1.

The optional occurrence argument allows you to specify which occurrence of the match to search for. If omitted, the first occurrence is used (occurrence 1).

The optional match_type argument allows you to refine the regular expression. For example, you can use this argument to specify case-sensitive matching or not.

Example 1 – Basic Usage

Here’s a basic example:

SELECT REGEXP_SUBSTR('Thailand or Cambodia', 'l.nd') Result;

Result:

+--------+
| Result |
+--------+
| land   |
+--------+

In this case there’s a match, and the substring is returned.

Example 2 – Multiple Matches

By default, if there are multiple matches within the string, the first one is returned (although you can specify another occurrence if needed. We’ll get to that soon):

SELECT REGEXP_SUBSTR('Lend for land', 'l.nd') Result;

Result:

+--------+
| Result |
+--------+
| Lend   |
+--------+

Example 3 – No Match

Here’s an example where there’s no match:

SELECT REGEXP_SUBSTR('Lend for land', '^C') Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

There’s no match, so the result is NULL.

Example 4 – The pos Argument

Here’s an example of specifying a starting position:

SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) Result;

Result:

+--------+
| Result |
+--------+
| Cut    |
+--------+

We started at position 2, which comes after the start of the first occurrence, so the next occurrence is returned instead.

Here’s another example:

SELECT 
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1) 'Pos 1',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) 'Pos 2',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 6) 'Pos 6';

Result:

+-------+-------+-------+
| Pos 1 | Pos 2 | Pos 6 |
+-------+-------+-------+
| Cat   | Cut   | Cot   |
+-------+-------+-------+

Example 5 – The occurrence Argument

Here’s an example of using the occurrence argument. In all cases, we start at position 1:

SELECT 
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 1) 'Occurrence 1',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 2) 'Occurrence 2',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 3) 'Occurrence 3';

Result:

+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
| Cat          | Cut          | Cot          |
+--------------+--------------+--------------+

However, if we start at a different position, the result is different:

SELECT 
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 1) 'Occurrence 1',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 2) 'Occurrence 2',
  REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2, 3) 'Occurrence 3';

Result:

+--------------+--------------+--------------+
| Occurrence 1 | Occurrence 2 | Occurrence 3 |
+--------------+--------------+--------------+
| Cut          | Cot          | NULL         |
+--------------+--------------+--------------+

This happened because our starting position came after the first occurrence had started. Therefore, occurrence 2 became occurrence 1, and occurrence 3 became occurrence 2. And because there were no more occurrences, the result of occurrence 3 was NULL (i.e. there was no occurrence 3).

Example 6 – 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_SUBSTR('Cat Cut Cot', 'c.t', 1, 1, 'c') 'Case-Sensitive',
  REGEXP_SUBSTR('Cat Cut Cot', 'c.t', 1, 1, 'i') 'Case-Insensitive';

Result:

+----------------+------------------+
| Case-Sensitive | Case-Insensitive |
+----------------+------------------+
| NULL           | Cat              |
+----------------+------------------+

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.

How NOT RLIKE Works in MySQL

In MySQL, NOT RLIKE is a negation of the RLIKE operator.

In other words, any time the RLIKE operator would return 1, NOT RLIKE will return 0.

Syntax

The syntax goes like this:

expr NOT RLIKE pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.
It’s the equivalent of doing the following:

NOT (expr RLIKE pat)

Example 1 – Basic Usage

Here’s an example of using this in a SELECT statement:

SELECT 'Coffee' NOT RLIKE '^C.*e$' AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Here, the pattern is matched if the input string starts with C and ends with e. It does, but because we use NOT RLIKE, we get a negative result (0).
The above statement is the equivalent of doing this:

SELECT NOT ('Coffee' RLIKE '^C.*e$') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 2 – Compared to RLIKE

Here we compare the results from RLIKE with NOT RLIKE:

SELECT 
  'Coffee' RLIKE '^C.*e$' AS 'RLIKE',
  'Coffee' NOT RLIKE '^C.*e$' AS 'NOT RLIKE';

Result:

+-------+-----------+
| RLIKE | NOT RLIKE |
+-------+-----------+
|     1 |         0 |
+-------+-----------+

Example 3 – A Positive Result

The previous examples resulted in 0 for NOT RLIKE, because the string did actually match the pattern. Here’s an example where we get a 1, which indicates that the string doesn’t match:

SELECT 
  'Funny' RLIKE '^C.*e$' AS 'RLIKE',
  'Funny' NOT RLIKE '^C.*e$' AS 'NOT RLIKE';

Result:

+-------+-----------+
| RLIKE | NOT RLIKE |
+-------+-----------+
|     0 |         1 |
+-------+-----------+

Alternatives

MySQL includes many functions and operators that essentially do the same thing, and this also applies to NOT RLIKE.
Firstly, RLIKE is a synonym of the REGEXP_LIKE() function (as is REGEXP).
Secondly, NOT RLIKE is the equivalent of NOT REGEXP.
Thirdly, RLIKE, REGEXP, and REGEXP_LIKE() can be negated by simply using the NOT logical operator.
Therefore, all of the following are equivalent:

expr NOT RLIKE pat
expr NOT REGEXP pat
NOT (expr RLIKE pat)
NOT (expr REGEXP pat)
NOT REGEXP_LIKE(expr, pat)

And here’s an example to demonstrate:

SELECT 
  'Car' NOT RLIKE '^C' AS 'Result 1',
  'Car' NOT REGEXP '^C' AS 'Result 2',
  NOT ('Car' RLIKE '^C') AS 'Result 3',
  NOT ('Car' REGEXP '^C') AS 'Result 4',
  NOT REGEXP_LIKE('Car', '^C') AS 'Result 5';

Result:

+----------+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 | Result 5 |
+----------+----------+----------+----------+----------+
|        0 |        0 |        0 |        0 |        0 |
+----------+----------+----------+----------+----------+

How NOT REGEXP Works in MySQL

In MySQL, NOT REGEXP is a negation of the REGEXP operator.

In other words, if the string matches the regular expression provided, the result is 0, otherwise it’s 1. This is the opposite result to what the REGEXP would return (when it isn’t prefixed with NOT).

Syntax

The syntax goes like this:

expr NOT REGEXP pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.
It’s the equivalent of doing the following:

NOT (expr REGEXP pat)

Example 1 – Basic Usage

Here’s an example of how to use this in a SELECT statement:

SELECT 'Bread' NOT REGEXP '^Br' AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Here, the pattern is matched if the input string starts with Br. It does, but because we use NOT REGEXP, we get a negative result (0).
The above statement is the equivalent of doing this:

SELECT NOT ('Bread' REGEXP '^Br') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 2 – Compared to REGEXP

To make it clear, here we compare the results from REGEXP and NOT REGEXP:

SELECT 
  'Bread' REGEXP '^Br' AS 'REGEXP',
  'Bread' NOT REGEXP '^Br' AS 'NOT REGEXP';

Result:

+--------+------------+
| REGEXP | NOT REGEXP |
+--------+------------+
|      1 |          0 |
+--------+------------+

Example 3 – A Positive Result

The previous examples resulted in 0 for NOT REGEXP, because the string did actually match the pattern. Here’s an example where we get a 1, which indicates that the string doesn’t match:

SELECT 
  'Sofa' REGEXP '^Br' AS 'REGEXP',
  'Sofa' NOT REGEXP '^Br' AS 'NOT REGEXP';

Result:

+--------+------------+
| REGEXP | NOT REGEXP |
+--------+------------+
|      0 |          1 |
+--------+------------+

How the RLIKE Operator Works in MySQL

In MySQL, the RLIKE operator is used to determine whether or not a string matches a regular expression. It’s a synonym for REGEXP_LIKE().

If the string matches the regular expression provided, the result is 1, otherwise it’s 0.

Syntax

The syntax goes like this:

expr RLIKE pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.

Example

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Tweet' REGEXP '^Tw.*t$';

Result:

+--------------------------+
| 'Tweet' REGEXP '^Tw.*t$' |
+--------------------------+
|                        1 |
+--------------------------+

In this case, the return value is 1 which means that the input string matched the regular expression. In particular, we specified that the input string should start with Tw and end with t (this is because we started the pattern with ^Tw and ended it with t$). The . part specifies any character, and * specifies that it could be zero to any number of that (any) character. So .* means that there can be no characters, one character, or many characters in between the start and end.
Here’s what happens if we drop the *:

SELECT 'Tweet' REGEXP '^Tw.t$';

Result:

+-------------------------+
| 'Tweet' REGEXP '^Tw.t$' |
+-------------------------+
|                       0 |
+-------------------------+

The return result is 0 which means no match. This is because . specifies only one instance of any character. Our input string contains two instances.
Here are some permutations:

SELECT 
  'Twet' REGEXP '^Tw.t$' AS 'Twet',
  'Twit' REGEXP '^Tw.t$' AS 'Twit',
  'Twt' REGEXP '^Tw.t$' AS 'Twt',
  'Tw.t' REGEXP '^Tw.t$' AS 'Tw.t';

Result:

+------+------+-----+------+
| Twet | Twit | Twt | Tw.t |
+------+------+-----+------+
|    1 |    1 |   0 |    1 |
+------+------+-----+------+

More Examples

For more examples, see MySQL REGEXP Examples. Like RLIKE, the REGEXP operator is also a synonym for REGEXP_LIKE().

MySQL REGEXP Examples

In MySQL, the REGEXP operator is used to determine whether or not a string matches a regular expression. It’s a synonym for REGEXP_LIKE().

If the string matches the regular expression provided, the result is 1, otherwise it’s 0.

Syntax

The syntax goes like this:

expr REGEXP pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.

Example 1 – Basic Regular Expression

The most basic regular expression we can use is one that has no special characters in it. Here, we just use a string. If any part of the input string matches that string, it returns a match.

SELECT 
  'Corn' REGEXP 'Corn' AS 'Corn',
  'Acorn' REGEXP 'Corn' AS 'Acorn',
  'Corner' REGEXP 'Corn' AS 'Corner',
  'Cheese' REGEXP 'Corn' AS 'Cheese';

Result:

+------+-------+--------+--------+
| Corn | Acorn | Corner | Cheese |
+------+-------+--------+--------+
|    1 |     1 |      1 |      0 |
+------+-------+--------+--------+

Example 2 – Match the Beginning of a String

In this example, the regular expression specifies that the string must begin with Co.

SELECT 
  'Corn' REGEXP '^Co' AS 'Corn',
  'Acorn' REGEXP '^Co' AS 'Acorn',
  'Cheese' REGEXP '^Co' AS 'Cheese';

Result:

+------+-------+--------+
| Corn | Acorn | Cheese |
+------+-------+--------+
|    1 |     0 |      0 |
+------+-------+--------+

Example 3 – Match the End of a String

In this example, the regular expression specifies that the string must end with rn.

SELECT 
  'Corn' REGEXP 'rn$' AS 'Corn',
  'Acorn' REGEXP 'rn$' AS 'Acorn',
  'Cheese' REGEXP 'rn$' AS 'Cheese';

Result:

+------+-------+--------+
| Corn | Acorn | Cheese |
+------+-------+--------+
|    1 |     1 |      0 |
+------+-------+--------+

Example 4 – Match Any Character

The . character matches any character.

SELECT 
  'Corn' REGEXP '.' AS 'Corn',
  'Cheese' REGEXP '.' AS 'Cheese',
  '' REGEXP '.' AS '';

Result:

+------+--------+---+
| Corn | Cheese |   |
+------+--------+---+
|    1 |      1 | 0 |
+------+--------+---+

This character is typically used in conjunction with other characters to specify further criteria. For example:

SELECT 
  'Corn' REGEXP '^C.rn$' AS 'Corn', 
  'Crn' REGEXP '^C.rn$' AS 'Crn';

Result:

+------+-----+
| Corn | Crn |
+------+-----+
|    1 |   0 |
+------+-----+

Here we specify that the string must start with C, that it must be followed by a character (any character), and that it must end with rn.
Note that this character specifies a single instance of the character. If you want to specify multiple instances (for example ee instead of just e), you’ll need to add more . characters.

SELECT 
  'Tweet' REGEXP '^Tw..t$' AS 'Tweet', 
  'Tweat' REGEXP '^Tw..t$' AS 'Tweat', 
  'Tweet' REGEXP '^Tw.t$' AS 'Tweet', 
  'Twit' REGEXP '^Tw..t$' AS 'Twit';

Result:

+-------+-------+-------+------+
| Tweet | Tweat | Tweet | Twit |
+-------+-------+-------+------+
|     1 |     1 |     0 |    0 |
+-------+-------+-------+------+

Another way to do this is to specify the number of occurrences within curly brackets:

SELECT 
  'Tweet' REGEXP '^Tw.{2}t$' AS 'Tweet', 
  'Tweat' REGEXP '^Tw.{2}t$' AS 'Tweat', 
  'Tweet' REGEXP '^Tw.{1}t$' AS 'Tweet', 
  'Twit' REGEXP '^Tw.{2}t$' AS 'Twit';

Result:

+-------+-------+-------+------+
| Tweet | Tweat | Tweet | Twit |
+-------+-------+-------+------+
|     1 |     1 |     0 |    0 |
+-------+-------+-------+------+

However, if you know the character that you’re looking for, you can specify that character (instead of the . character), as demonstrated in the following example.

Example 5 – Match Zero or More Instances of a Specific Character

We can do the following to specify zero or more instances of the e character:

SELECT 
  'Twet' REGEXP '^Twe*t$' AS 'Twet',
  'Tweet' REGEXP '^Twe*t$' AS 'Tweet',
  'Tweeet' REGEXP '^Twe*t$' AS 'Tweeet',
  'Twt' REGEXP '^Twe*t$' AS 'Twt',
  'Twit' REGEXP '^Twe*t$' AS 'Twit',
  'Twiet' REGEXP '^Twe*t$' AS 'Twiet',
  'Tweit' REGEXP '^Twe*t$' AS 'Tweit';

Result:

+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
|    1 |     1 |      1 |   1 |    0 |     0 |     0 |
+------+-------+--------+-----+------+-------+-------+

The first four match but the last three don’t.

Example 6 – Match One or More Instances of a Specific Character

We can modify the previous example so that we only get a match if one or more characters is found (the previous example returned a match if zero or more were found). To do this, we simply use + instead of *:

SELECT 
  'Twet' REGEXP '^Twe+t$' AS 'Twet',
  'Tweet' REGEXP '^Twe+t$' AS 'Tweet',
  'Tweeet' REGEXP '^Twe+t$' AS 'Tweeet',
  'Twt' REGEXP '^Twe+t$' AS 'Twt',
  'Twit' REGEXP '^Twe+t$' AS 'Twit',
  'Twiet' REGEXP '^Twe+t$' AS 'Twiet',
  'Tweit' REGEXP '^Twe+t$' AS 'Tweit';

Result:

+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
|    1 |     1 |      1 |   0 |    0 |     0 |     0 |
+------+-------+--------+-----+------+-------+-------+

In this case, the fourth word returns a different result to the previous example.

Example 7 – Match Zero or One Instance of a Specific Character

We can modify the previous example so that we only get a match on zero or one of the desired characters. To do this, we use ?:

SELECT 
  'Twet' REGEXP '^Twe?t$' AS 'Twet',
  'Tweet' REGEXP '^Twe?t$' AS 'Tweet',
  'Tweeet' REGEXP '^Twe?t$' AS 'Tweeet',
  'Twt' REGEXP '^Twe?t$' AS 'Twt',
  'Twit' REGEXP '^Twe?t$' AS 'Twit',
  'Twiet' REGEXP '^Twe?t$' AS 'Twiet',
  'Tweit' REGEXP '^Twe?t$' AS 'Tweit';

Result:

+------+-------+--------+-----+------+-------+-------+
| Twet | Tweet | Tweeet | Twt | Twit | Twiet | Tweit |
+------+-------+--------+-----+------+-------+-------+
|    1 |     0 |      0 |   1 |    0 |     0 |     0 |
+------+-------+--------+-----+------+-------+-------+

Example 8 – Alternation

You can use the | character to match one or another sequence of characters:

SELECT 
  'Tweet' REGEXP 'Tw|et' AS 'Tweet',
  'For Let' REGEXP 'Tw|et' AS 'For Let',
  'Banana' REGEXP 'Tw|et' AS 'Banana';

Result:

+-------+---------+--------+
| Tweet | For Let | Banana |
+-------+---------+--------+
|     1 |       1 |      0 |
+-------+---------+--------+

Here’s another example where I search for whole words:

SELECT 
  'Cat' REGEXP 'Cat|Dog' AS 'Cat',
  'Dog' REGEXP 'Cat|Dog' AS 'Dog',
  'Doggone' REGEXP 'Cat|Dog' AS 'Doggone',
  'Banana' REGEXP 'Cat|Dog' AS 'Banana';

Result:

+-----+-----+---------+--------+
| Cat | Dog | Doggone | Banana |
+-----+-----+---------+--------+
|   1 |   1 |       1 |      0 |
+-----+-----+---------+--------+

We still get a match even when our regular expression matches only part of the the string.

Example 9 – Match Zero or More Instances of a Sequence

You can use brackets along with the asterisk ()* to specify zero or more instances of a sequence:

SELECT 
  'Banana' REGEXP '(an)*' AS 'Banana',
  'Land' REGEXP '(an)*' AS 'Land',
  'Cheese' REGEXP '(an)*' AS 'Cheese';

Result:

+--------+------+--------+
| Banana | Land | Cheese |
+--------+------+--------+
|      1 |    1 |      1 |
+--------+------+--------+

Another example:

SELECT 
  'Banana' REGEXP '^B(an)*d$' AS 'Banana',
  'Band' REGEXP '^B(an)*d$' AS 'Band',
  'Bald' REGEXP '^B(an)*d$' AS 'Bald',
  'Bad' REGEXP '^B(an)*d$' AS 'Bad';

Result:

+--------+------+------+-----+
| Banana | Band | Bald | Bad |
+--------+------+------+-----+
|      0 |    1 |    0 |   0 |
+--------+------+------+-----+

Example 10 – Repetition

As seen in a previous example, you can use curly brackets to specify repetition. This notation provides a more general way of writing regular expressions than some of the previous examples:

SELECT 
  'Tweeet' REGEXP 'e{3}' AS 'Tweeet',
  'Tweet' REGEXP 'e{3}' AS 'Tweet';

Result:

+--------+-------+
| Tweeet | Tweet |
+--------+-------+
|      1 |     0 |
+--------+-------+

Example 11 – Range

You can use the hyphen character to specify a range. Here’s an example that specifies a range of numbers:

SELECT 
  'Tweet 123' REGEXP '[0-9]' AS 'Tweet 123',
  'Tweet ABC' REGEXP '[0-9]' AS 'Tweet ABC';

Result:

+--------+-------+
| Tweeet | Tweet |
+--------+-------+
|      1 |     0 |
+--------+-------+

And the following example specifies a range of letters:

SELECT 
  'Tweet 123' REGEXP '[A-Z]' AS 'Tweet 123',
  'ABC' REGEXP '[A-Z]' AS 'ABC',
  '123' REGEXP '[A-Z]' AS '123';

Result:

+--------+-------+
| Tweeet | Tweet |
+--------+-------+
|      1 |     0 |
+--------+-------+

Here’s what happens if we limit the range of numbers:

SELECT 
  '123' REGEXP '[1-3]' AS '123',
  '012' REGEXP '[1-3]' AS '012',
  '045' REGEXP '[1-3]' AS '045';

Result:

+-----+-----+-----+
| 123 | 012 | 045 |
+-----+-----+-----+
|   1 |   1 |   0 |
+-----+-----+-----+

Example 12 – Not Within a Range

We can use the ^ character to modify the previous example so that the specified range of characters are excluded:

SELECT 
  '123' REGEXP '[^1-3]' AS '123',
  '012' REGEXP '[^1-3]' AS '012',
  '045' REGEXP '[^1-3]' AS '045';

Result:

+-----+-----+-----+
| 123 | 012 | 045 |
+-----+-----+-----+
|   0 |   1 |   1 |
+-----+-----+-----+

So we get the opposite result to the previous example.

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.

Mysql: Pattern Matching with Regular Expressions

Problem

You want to perform a pattern match rather than a literal comparison.

Solution

Use the REGEXP operator and a regular expression pattern, described in this section.

Discussion

SQL patterns are likely to be implemented by other database systems, so they’re reasonably portable beyond MySQL. On the other hand, they’re somewhat limited. For example, you can easily write a SQL pattern %abc% to find strings that contain abc, but you cannot write a single SQL pattern to identify strings that contain any of the characters a, b, or c. Nor can you match string content based on character types such as letters or digits. For such operations, MySQL supports another type of pattern matching operation based on regular expressions and the REGEXP operator (or NOT REGEXP to reverse the sense of the match).^[24] REGEXP matching uses a different set of pattern elements than % and _ (neither of which is special in regular expressions):

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
p1|p2|p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{n}	n instances of preceding element
{m,n}	m through n instances of preceding element

You may already be familiar with these regular expression pattern characters, because many of them are the same as those used by vi, grep, sed, and other Unix utilities that support regular expressions. Most of them are used also in the regular expressions understood by Perl, PHP, and Python. (For example, Chapter 10 discuss pattern matching in Perl scripts.) For Java, the Jakarta ORO or Regexp class libraries provide matching capabilities that use these characters as well.

The previous section on SQL patterns showed how to match substrings at the beginning or end of a string, or at an arbitrary or specific position within a string. You can do the same things with regular expressions:

• Strings that begin with a particular substring:

  mysql> SELECT name FROM metal WHERE name REGEXP '^co';
  +--------+
  | name   |
  +--------+
  | copper |
  +--------+

• Strings that end with a particular substring:

  mysql> SELECT name FROM metal WHERE name REGEXP 'er$';
  +--------+
  | name   |
  +--------+
  | copper |
  | silver |
  +--------+

• Strings that contain a particular substring at any position:

  mysql> SELECT name FROM metal WHERE name REGEXP 'er';
  +---------+
  | name    |
  +---------+
  | copper  |
  | mercury |
  | silver  |
  +---------+

• Strings that contain a particular substring at a specific position:

  mysql> SELECT name FROM metal WHERE name REGEXP '^..pp';
  +--------+
  | name   |
  +--------+
  | copper |
  +--------+

In addition, regular expressions have other capabilities and can perform kinds of matches that SQL patterns cannot. For example, regular expressions can contain character classes, which match any character in the class:

• To write a character class, list the characters you want the class to match inside square brackets. Thus, the pattern [abc] matches either a, b, or c.
• Classes may indicate ranges of characters by using a dash between the beginning and end of the range. [a-z] matches any letter, [0-9] matches digits, and [a-z0-9] matches letters or digits.
• To negate a character class (“match any character but these”), begin the list with a ^ character. For example, [^0-9] matches anything but digits.

MySQL’s regular expression capabilities also support POSIX character classes. These match specific character sets, as described in the following table.

POSIX class	What the class matches
[:alnum:]	Alphabetic and numeric characters
[:alpha:]	Alphabetic characters
[:blank:]	Whitespace (space or tab characters)
[:cntrl:]	Control characters
[:digit:]	Digits
[:graph:]	Graphic (non-blank) characters
[:lower:]	Lowercase alphabetic characters
[:print:]	Graphic or space characters
[:punct:]	Punctuation characters
[:space:]	Space, tab, newline, carriage return
[:upper:]	Uppercase alphabetic characters
[:xdigit:]	Hexadecimal digits (0-9, a-f, A-F)

POSIX classes are intended for use within character classes, so you use them within square brackets. The following expression matches values that contain any hexadecimal digit character:

mysql> SELECT name, name REGEXP '[[:xdigit:]]' FROM metal;
+----------+----------------------------+
| name     | name REGEXP '[[:xdigit:]]' |
+----------+----------------------------+
| copper   |                          1 |
| gold     |                          1 |
| iron     |                          0 |
| lead     |                          1 |
| mercury  |                          1 |
| platinum |                          1 |
| silver   |                          1 |
| tin      |                          0 |
+----------+----------------------------+

Regular expressions can contain alternations. The syntax looks like this:

alternative1|alternative2|...

An alternation is similar to a character class in the sense that it matches if any of the alternatives match. But unlike a character class, the alternatives are not limited to single characters—they can be strings or even patterns. For example, the following alternation matches strings that begin with a vowel or end wither:

mysql> SELECT name FROM metal WHERE name REGEXP '^[aeiou]|er$';
+--------+
| name   |
+--------+
| copper |
| iron   |
| silver |
+--------+

Parentheses may be used to group alternations. For example, if you want to match strings that consist entirely of digits or entirely of letters, you might try this pattern, using an alternation:

mysql> SELECT '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$';
+-------------------------------------------+
| '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$' |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+

But as the query result shows, the pattern doesn’t work. That’s because the ^groups with the first alternative, and the $ groups with the second alternative. So the pattern actually matches strings that begin with one or more digits, or strings that end with one or more letters. However, if you group the alternatives within parentheses, the ^ and $ will apply to both of them and the pattern will act as you expect:

mysql> SELECT '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$';
+---------------------------------------------+
| '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$' |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

Unlike SQL pattern matches, which are successful only if the pattern matches the entire comparison value, regular expressions are successful if the pattern matches anywhere within the value. The following two pattern matches are equivalent in the sense that each one succeeds only for strings that contain a bcharacter, but the first is more efficient because the pattern is simpler:

'abc' REGEXP 'b'
'abc' REGEXP '^.*b.*$'

Regular expressions do not match NULL values. This is true both for REGEXPand for NOT REGEXP:

mysql> SELECT NULL REGEXP '.*', NULL NOT REGEXP '.*';
+------------------+----------------------+
| NULL REGEXP '.*' | NULL NOT REGEXP '.*' |
+------------------+----------------------+
|             NULL |                 NULL |
+------------------+----------------------+

The fact that a regular expression matches a string if the pattern is found anywhere in the string means you must take care not to inadvertently specify a pattern that matches the empty string. If you do, it will match any non-NULLvalue at all. For example, the pattern a* matches any number of a characters, even none. If your goal is to match only strings containing nonempty sequences of a characters, use a+ instead. The + requires one or more instances of the preceding pattern element for a match.

As with SQL pattern matches performed using LIKE, regular expression matches performed with REGEXP sometimes are equivalent to substring comparisons. The ^ and $ metacharacters serve much the same purpose as LEFT( ) or RIGHT( ), at least if you’re looking for literal strings:

Pattern match	Substring comparison
str REGEXP '^abc'	LEFT(str,3) = 'abc'
str REGEXP 'abc$'	RIGHT(str,3) = 'abc'

For non-literal strings, it’s typically not possible to construct an equivalent substring comparison. For example, to match strings that begin with any nonempty sequence of digits, you can use this pattern match:

str REGEXP '^[0-9]+'

That is something that LEFT( ) cannot do (and neither can LIKE, for that matter).