Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Operators and Predicates

SQL regular expression matching

Reference for regular expression matching in SQL: REGEXP, RLIKE, ~, REGEXP_LIKE and SIMILAR TO across databases, and SQLite's missing REGEXP function.

Regular expressions describe text patterns far more precisely than LIKE: repetition, alternatives, anchors, groups and character classes. Regex matching is not part of the core SQL standard in a portable form, so every database spells it differently, and SQLite ships the syntax without the function.

In SQLite, x REGEXP y is rewritten to a call of a user-defined function regexp(y, x). Stock SQLite does not define that function, so the operator fails until your application registers one. Our in-browser SQLite is the same, which is why the runnable examples here use LIKE and GLOB instead.

SQL
-- PostgreSQL
SELECT email FROM customers WHERE email ~ '^[a-m].*@example\.com$';

Try it

SQL REGEXP in SQLite without a registered function (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The run ends with no such function: REGEXP. The portable-enough replacement for simple patterns is LIKE or GLOB:

SQL Simple patterns without regex ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- PostgreSQL
expression ~ pattern          -- match, case sensitive
expression ~* pattern         -- match, case insensitive
expression !~ pattern         -- no match
-- MySQL, MariaDB, SQLite (with a user function)
expression [NOT] REGEXP pattern
expression [NOT] RLIKE pattern
-- Oracle, MySQL 8, MariaDB
REGEXP_LIKE(expression, pattern [, flags])
-- SQL standard
expression [NOT] SIMILAR TO pattern

Dialect differences

Database Operator or function Regex flavor Notes
PostgreSQL ~, ~*, !~, !~*, SIMILAR TO, regexp_match(), regexp_replace() POSIX extended (ARE) Matches anywhere in the string unless anchored. Index with pg_trgm.
MySQL 8 REGEXP, RLIKE, REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR() ICU Case insensitivity depends on the collation, or use the flag 'i'.
MariaDB REGEXP, RLIKE PCRE Slightly different syntax from MySQL 8.
SQLite REGEXP operator None built in Register a regexp() function in your application, or load an extension.
SQL Server None None Use LIKE with [a-z] classes, or a CLR function. Newer releases add regex functions; check your version.
Oracle REGEXP_LIKE(), REGEXP_SUBSTR(), REGEXP_REPLACE(), REGEXP_INSTR() POSIX-style Pass 'i' as the flag for case-insensitive matching.
SQL
-- MySQL 8 and Oracle
SELECT email FROM customers WHERE REGEXP_LIKE(email, '^[a-m]');

-- PostgreSQL: SIMILAR TO uses SQL wildcards plus a few regex features
SELECT name FROM products WHERE name SIMILAR TO '(Chef|Cast)%';

-- SQLite: registering a regexp function from Python
-- import re
-- conn.create_function("regexp", 2, lambda pattern, text: re.search(pattern, text) is not None)

Usage notes

  • Unanchored by default. ~ 'rosa' matches any email containing "rosa". Add ^ and $ for a full match.
  • Backslashes need care. Whether '\.' reaches the regex engine intact depends on string-literal rules: MySQL treats backslash as an escape in string literals (so you write '\\.'), while standard-conforming PostgreSQL strings do not.
  • NULL in, NULL out. If either side is NULL, the result is NULL and WHERE drops the row.
  • Speed: a regex cannot use a normal B-tree index, so it scans rows. Trigram or full-text indexes (FTS5, pg_trgm) help for substring-heavy searches.
  • Prefer simpler tools when they are enough. LIKE, GLOB and INSTR() are portable and index-friendlier.
  • Catastrophic patterns such as nested quantifiers can take very long on some engines. Do not let users supply their own patterns.