SQL REGEXP

What is the SQL REGEXP operator?

REGEXP is a pattern-matching operator that returns true when a string matches a supplied regular expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REGEXP: MySQL, MariaDB (native). PostgreSQL (~ operator instead). SQLite (requires custom compile option). Oracle and SQL Server expose similar capability via REGEXP_LIKE but not the REGEXP operator.

SQL REGEXP Full Explanation

REGEXP (also written as RLIKE) is a comparison operator that evaluates whether a character expression matches a regular expression pattern. It returns 1 (true) when the pattern matches and 0 (false) otherwise. REGEXP supports anchors (^, $), character classes ([a-z]), quantifiers (*, +, ?), alternation (|), grouping (()), and escapes (\\). Case sensitivity depends on collation in MySQL; use a *_bin collation for case-sensitive matches. NOT REGEXP negates the result. In contrast to LIKE, REGEXP offers full regular expression power, making it suitable for complex string validations and searches. Performance can degrade on large text columns without indexes. In MySQL 8+, REGEXP uses the International Components for Unicode (ICU) library, enabling Unicode-aware matching.

SQL REGEXP Syntax

-- basic
string_expression REGEXP pattern_expression;

-- negation
string_expression NOT REGEXP pattern_expression;

SQL REGEXP Parameters

string_expression (STRING) - The text to test.
pattern_expression (STRING) - A valid regular expression to compare against.

Example Queries Using SQL REGEXP

-- 1. Find emails with a gmail domain
SELECT email
FROM users
WHERE email REGEXP '@gmail\\.com$';

-- 2. Validate US phone numbers (simple)
SELECT phone_number
FROM contacts
WHERE phone_number REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

-- 3. Case-sensitive match for "Galaxy"
SELECT name
FROM products
WHERE name COLLATE utf8mb4_bin REGEXP '^Galaxy$';

-- 4. Use NOT REGEXP to exclude rows
SELECT username
FROM accounts
WHERE username NOT REGEXP '^[a-z0-9_]+$';

Expected Output Using SQL REGEXP

Each query returns only those rows where the column value satisfies (or fails, with NOT) the specified regular expression
The result set is filtered accordingly

Use Cases with SQL REGEXP

Validate complex string formats such as emails, phone numbers, or IDs
Search text columns for flexible patterns the LIKE operator cannot express
Clean data by identifying rows that do not conform to expected formats
Segment users based on partial text found within a column
Perform case-insensitive or case-sensitive searches depending on collation

Common Mistakes with SQL REGEXP

Forgetting to escape special characters like the dot (.) or backslash (\\)
Expecting LIKE wildcards (% and _) to work inside REGEXP patterns
Assuming case-sensitive behavior without changing collation to *_bin
Using REGEXP on very large text columns without understanding performance impact
Misplacing anchors (^, $) leading to broader matches than intended

First Introduced In

MySQL 3.23

Frequently Asked Questions

Is REGEXP the same as RLIKE?

Yes. In MySQL and MariaDB, REGEXP and RLIKE are synonyms and behave identically.

Can I use capturing groups in a REGEXP filter?

Yes. Although REGEXP in a WHERE clause only returns true or false, you can still use parentheses for grouping and alternation inside the pattern.

How do I force case-sensitive regular-expression matching?

Apply a binary collation to the string column or pattern, for example: `WHERE name COLLATE utf8mb4_bin REGEXP '^Galaxy$'`.

Why does my dot (.) match any character including a literal dot?

In regular expressions, dot is a wildcard. Escape it with a backslash (\\.) when you want a literal period.