MySQL Regular Expressions (Regexp)

What are MySQL regular expressions?

MySQL regular expressions help you search for data that matches complex criteria. A regular expression is a pattern that describes the shape of the value you are looking for, rather than the value itself.

If you have already worked with MySQL wildcards, you may ask why regular expressions are worth learning when LIKE produces similar results. The answer is expressive power: wildcards offer only two symbols, while regular expressions describe character ranges, alternatives, repetition, and word positions in a single pattern.

With the purpose established, the next section introduces the syntax you will use in every REGEXP query.

Basic syntax of REGEXP

The basic syntax for a regular expression is as follows.

SELECT * FROM table_name
WHERE fieldname REGEXP 'pattern';

HERE:

• “SELECT statement” is the standard SELECT statement.
• “WHERE fieldname” is the name of the column on which the regular expression is performed.
• “REGEXP ‘pattern'” — REGEXP is the regular expression operator, and ‘pattern’ represents the pattern to be matched. RLIKE is a synonym for REGEXP and returns the same results. To avoid confusing it with the LIKE operator, it is better to use REGEXP.

Let us now look at a practical example.

SELECT * FROM `movies`
WHERE `title` REGEXP 'code';

The above query searches for all the movie titles that contain the word “code”. It does not matter whether “code” appears at the beginning, in the middle, or at the end of the title. As long as the title contains the pattern, the row is returned.

Matching the start of a value with a character list

Suppose we want the movies whose titles start with a, b, c, or d, followed by any number of other characters. We combine a character list with the caret metacharacter to achieve that result.

SELECT * FROM `movies`
WHERE `title` REGEXP '^[abcd]';

Executing the above script in MySQL Workbench against the myflixdb database gives us the following results.

In the pattern ‘^[abcd]’, the caret (^) requires the match to start at the beginning of the value, and the character list [abcd] accepts only titles whose first letter is a, b, c, or d. The comparison is not case sensitive under the default collation, which is why “Code Name Black” is returned.

Excluding characters with a negated character list

Let us now modify the script and negate the character list to see which rows are returned.

SELECT * FROM `movies`
WHERE `title` REGEXP '^[^abcd]';

Executing the above script in MySQL Workbench against the myflixdb database gives us the following results.

Inside a character list, the caret changes meaning: ‘^[^abcd]’ still anchors the match at the beginning, while [^abcd] now excludes every title that starts with one of the enclosed characters.

These two examples use only anchors and character lists. The next section covers the full metacharacter set.

Regular expression metacharacters

The examples above show the simplest form of a regular expression. Metacharacters allow you to fine-tune a pattern search: they express repetition, alternatives, ranges, and positions. The table below lists every metacharacter supported by the MySQL REGEXP operator, with a corrected example for each one.

The backslash (\) is the escape character. Because MySQL parses the string first and the pattern second, a literal backslash must be written as a double backslash (\\) inside a REGEXP pattern.

Now that every metacharacter is defined, a fair question follows: when should REGEXP replace the simpler LIKE operator?

REGEXP vs LIKE: which one should you use?

Both operators filter rows by pattern, but they solve different problems. LIKE understands two symbols only, while REGEXP understands the full metacharacter set shown above. That power has a cost, so the choice is a trade-off rather than a preference.

Choose LIKE for straightforward matching, because it reads well and can still use an index. Choose REGEXP when a single pattern must express several rules at once, such as “starts with c or d and ends with a digit”. On large tables, narrow the rows first with an indexed condition, then apply REGEXP to that smaller set.

MySQL 8.0 regular expression functions

The REGEXP operator answers only one question: does the value match the pattern? MySQL 8.0 added four functions that go further and let you locate, extract, and rewrite the matching text. Each one accepts an optional match_type argument, where ‘c’ forces a case-sensitive comparison and ‘i’ forces a case-insensitive one.

• REGEXP_LIKE(expr, pattern) returns 1 when the value matches the pattern. It is the function form of the REGEXP operator, and the match_type argument makes case sensitivity explicit.
• REGEXP_INSTR(expr, pattern) returns the position of the first character of the match, or 0 when the pattern is not found.
• REGEXP_SUBSTR(expr, pattern) returns the matching substring itself, which is useful for pulling a year, a code, or a number out of a longer text value.
• REGEXP_REPLACE(expr, pattern, replacement) returns the value with every match replaced, so it can clean data inside a SQL UPDATE query.

SELECT title,
       REGEXP_SUBSTR(title, '[0-9]+') AS number_in_title
FROM `movies`
WHERE REGEXP_LIKE(title, '[0-9]');

The query above returns every movie title that contains a digit, together with the digits themselves. On MySQL 5.7 these functions are unavailable, so the REGEXP operator remains the only option.

FAQs