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.
-- PostgreSQL SELECT email FROM customers WHERE email ~ '^[a-m].*@example\.com$';
Try it
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:
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- 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. |
-- 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. NULLin,NULLout. If either side isNULL, the result isNULLandWHEREdrops 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,GLOBandINSTR()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.