MySQL REGEXP

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

LIKE describes a shape with 2 characters. REGEXP describes one with a whole pattern language, so it can ask for things LIKE cannot express: a choice between alternatives, a repeated group, a character range. Open the client with mysql -u root -p sakila before you start.

How REGEXP works

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME REGEXP 'PATTERN_TEXT';

Replace PATTERN_TEXT with the regular expression to look for. The condition is true when the pattern matches anywhere inside the value. That is the first difference from LIKE, which has to describe the whole value. The pieces used below:

Piece	Means
`^`	the start of the value
`$`	the end of the value
`.`	any 1 character
`*`	the thing before it, repeated any number of times
`[AB]`	any 1 of the characters in the brackets
`(AL\|BE)`	either alternative

Examples

1) Starts with

SELECT title FROM film WHERE title REGEXP '^ACA' ORDER BY title;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.01 sec)

Without ^ the pattern would match ACA anywhere in the title.

2) Ends with

SELECT title FROM film WHERE title REGEXP 'GOLDFINGER$' ORDER BY title;

+----------------------+
| title                |
+----------------------+
| ACE GOLDFINGER       |
| BREAKFAST GOLDFINGER |
| SILVERADO GOLDFINGER |
+----------------------+
3 rows in set (0.00 sec)

3) Either of 2 beginnings

SELECT first_name FROM actor WHERE first_name REGEXP '^(AL|BE)' ORDER BY first_name LIMIT 5;

+------------+
| first_name |
+------------+
| AL         |
| ALAN       |
| ALBERT     |
| ALBERT     |
| ALEC       |
+------------+
5 rows in set (0.00 sec)

ALBERT appears twice because 2 actors share the name. SELECT reports rows, not distinct values.

4) A shape LIKE cannot describe

This asks for titles that begin with A or B and end with S.

SELECT title FROM film WHERE title REGEXP '^[AB].*S$' ORDER BY title LIMIT 5;

+----------------------+
| title                |
+----------------------+
| ADAPTATION HOLES     |
| AMELIE HELLFIGHTERS  |
| AMERICAN CIRCUS      |
| ANACONDA CONFESSIONS |
| ANALYZE HOOSIERS     |
+----------------------+
5 rows in set (0.00 sec)

5) Everything that does not match

NOT REGEXP reverses the test, the same way NOT LIKE does.

SELECT title, rating
FROM film
WHERE title NOT REGEXP '^A'
ORDER BY title
LIMIT 5;

+---------------------+--------+
| title               | rating |
+---------------------+--------+
| BABY HALL           | NC-17  |
| BACKLASH UNDEFEATED | PG-13  |
| BADMAN DAWN         | R      |
| BAKED CLEOPATRA     | G      |
| BALLOON HOMEWARD    | NC-17  |
+---------------------+--------+
5 rows in set (0.00 sec)

A NULL value matches neither REGEXP nor NOT REGEXP, because both return NULL for it. IS NULL covers that.

RLIKE is the same operator

SELECT title FROM film WHERE title RLIKE '^ACA' ORDER BY title;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

RLIKE and REGEXP are 2 spellings of one operator. Pick one and keep to it.

Case, and how to insist on it

A pattern follows the collation of what it is matching, the same as LIKE. So a lower case pattern matches upper case text by default. REGEXP_LIKE() is the function form of the operator, and its third argument overrides that: 'c' means case-sensitive.

SELECT REGEXP_LIKE('ACADEMY DINOSAUR', '^aca') AS default_mode,
       REGEXP_LIKE('ACADEMY DINOSAUR', '^aca', 'c') AS case_sensitive;

+--------------+----------------+
| default_mode | case_sensitive |
+--------------+----------------+
|            1 |              0 |
+--------------+----------------+
1 row in set (0.00 sec)

Prefer LIKE when LIKE can say it

No index can narrow a REGEXP search, because a match may start anywhere, so the server tests every value. LIKE with a pattern that starts with fixed text can jump straight to the matching part of an index. When both can express the filter, LIKE is the cheaper of the 2. The LIKE lesson explains which patterns can use an index and which cannot.

Summary

Use REGEXP to match a pattern anywhere in a value.
Anchor with ^ and $ when the match has to be at the start or the end.
Read RLIKE as another name for REGEXP.
Use REGEXP_LIKE(value, pattern, 'c') to force a case-sensitive match.
Reach for LIKE first, because no index can narrow a REGEXP search.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How REGEXP works

Examples

1) Starts with

2) Ends with

3) Either of 2 beginnings

4) A shape LIKE cannot describe

5) Everything that does not match

RLIKE is the same operator

Case, and how to insist on it

Prefer LIKE when LIKE can say it

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How REGEXP works

​Examples

​1) Starts with

​2) Ends with

​3) Either of 2 beginnings

​4) A shape LIKE cannot describe

​5) Everything that does not match

​RLIKE is the same operator

​Case, and how to insist on it

​Prefer LIKE when LIKE can say it

​Summary

​See also

How REGEXP works

Examples

1) Starts with

2) Ends with

3) Either of 2 beginnings

4) A shape LIKE cannot describe

5) Everything that does not match

RLIKE is the same operator

Case, and how to insist on it

Prefer LIKE when LIKE can say it

Summary

See also