MySQL REGEXP Guide: Match, Extract, Replace, and Performance

In MySQL, use LIKE 'prefix%' for simple prefix matching. Use regular expressions only when the pattern cannot be expressed clearly with % and _. MySQL 8 provides ICU-based regular expression functions such as REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR.

Match rows

SELECT id, email
FROM users
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@example\\.com$');

The older operator form also works:

SELECT id, username
FROM users
WHERE username REGEXP '^[a-z][a-z0-9_]{2,19}$';

RLIKE is a synonym for REGEXP. Teams should standardize on one style. Use REGEXP_LIKE when you need explicit match options.

Common patterns

SQL strings and regex syntax can both treat backslashes specially. To match a literal dot, you may need '\\.'. Always test escaping under your MySQL version and SQL mode.

Case sensitivity

Default matching can depend on character set and collation. Use match type when you need clarity:

SELECT REGEXP_LIKE('FreeMac', 'freemac', 'i'); -- case-insensitive
SELECT REGEXP_LIKE('FreeMac', 'freemac', 'c'); -- case-sensitive

Do not assume every database and every column has the same case behavior.

Extract matching content

SELECT REGEXP_SUBSTR('Order: FM-2026-0716', 'FM-[0-9]{4}-[0-9]{4}') AS code;

Find the first match position:

SELECT REGEXP_INSTR('macOS 26 guide', '[0-9]+') AS first_digit_position;

Check no-match behavior before using the result in string slicing or updates.

Replace and clean data

SELECT REGEXP_REPLACE('Free   Mac   Guide', '[[:space:]]+', ' ') AS normalized;

Regex replacement in the database is useful for small corrections and clear cleanup jobs. Before a large UPDATE, preview affected rows with SELECT, run inside a transaction where appropriate, and make sure backups exist.

Performance boundary

This is usually easier for indexes:

WHERE slug LIKE 'mac-%'

Regex predicates often inspect more rows:

WHERE REGEXP_LIKE(slug, '^mac-')

Optimization order:

1. Use normal predicates to narrow the dataset first.
2. Avoid regex scans over large tables.
3. Extract stable, frequently queried structure into columns.
4. Use regex for validation and cleanup, not as the only query plan for core traffic.

Portability warning

Do not copy regex SQL between databases blindly. PostgreSQL uses operators such as ~ and ~*. SQLite does not provide the same built-in REGEXP behavior by default. Engines, flags, escaping, and functions differ.

Related FreeMac guides

• For JavaScript data processing, read JavaScript Array Methods: map, splice, sort, and for...of.
• For JavaScript regex-adjacent string handling, read JavaScript Optional Chaining: When to Use ?..
• For backend API content modeling, read Strapi 5 Getting Started: Content Models, APIs, and Permissions.