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.