Mastering Advanced SQL Techniques with MySQL/

...

/

Regular Expressions

Regular Expressions

Advance your knowledge about pattern matching in strings with regular expressions in MySQL.

We'll cover the following...

Regular expressions, also known as regex, specify a search pattern to match a text. In technical terms, we search for occurrences of a string (i.e., the regular expression) in another string (i.e., the text). Thinking of basic pattern matching in MySQL, we can search for exterior and interior car parts in our running example of car models:

Press + to interact
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);
-- Inspect the properties of the table for car parts.
DESCRIBE CarPart;

As we can observe, CarPart has three columns, model, name, and built_at, of which the second represents the name of a car part using the data type TEXT. The following example searches for exterior and interior car parts using basic pattern matching, while implicitly specifying a regular expression:

Press + to interact
-- Retrieve all exterior and interior car parts.
SELECT *
FROM CarPart
WHERE name LIKE '%terior';

The string '%terior' (line 4) specifies a pattern we can think of as a regular expression. However, the syntax for regular expressions, as implemented in many programming languages, differs from basic pattern matching.

Theory

Before exploring regular expressions in MySQL, we want to understand the language-agnostic theory behind regex. Four fundamental concepts are involved: wildcard, alternation, grouping, and quantification.

Wildcard

The wildcard . matches any character from the considered character set (e.g., ASCII). For example, the regular expression a.c matches any of the following texts: ...

Access this course and 1400+ top-rated courses and projects.