REGEX

Query Techniques
Buy Me A Coffee

Match strings against regular expression patterns, enabling flexible search beyond simple LIKE wildcards.

Matches strings against regular expressions for complex pattern matching. Syntax and capabilities vary significantly across databases (POSIX in PostgreSQL, ICU in MySQL 8+, T-SQL in SQL Server). Performance can degrade on complex patterns over large text.

Compatibility

Filter by Database
SQL REGEX Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all REGEXP and RLIKE operators for matching. REGEXP_LIKE(col, pattern [, flags]), REGEXP_SUBSTR(), REGEXP_REPLACE(), REGEXP_INSTR() added in 8.0. Uses ICU regex library (8.0+), POSIX before that. Case-insensitive by default.
PostgreSQL Native all Rich operator set: ~ (match, case-sensitive), ~* (match, case-insensitive), !~ (no match), !~* (no match, case-insensitive). SIMILAR TO for SQL-standard regex. regexp_match(), regexp_matches(), regexp_replace(), regexp_split_to_table() as functions.
SQL Server Not Supported - No native regex support in T-SQL. LIKE with [] character class provides limited pattern matching. Full regex requires CLR integration (SQLCLR) or application-layer filtering.
Oracle Native 10g REGEXP_LIKE(col, pattern [, flags]) for WHERE clauses. Also REGEXP_SUBSTR(), REGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_COUNT(). Uses POSIX Extended Regular Expressions. Case-sensitive by default; use 'i' flag for case-insensitive.
SQLite Not Supported - The REGEXP operator exists in the syntax but has no built-in implementation — it requires a user-defined function to be registered at connection time. Most SQLite drivers (Python's sqlite3, etc.) do not register one by default.

Details

SQL Server is the major holdout — no native regex means workarounds are LIKE with limited [] syntax or moving regex logic to the application. PostgreSQL's operator-based approach (~ / ~* / !~ / !~*) is the most concise. SQLite's REGEXP is a syntax trap — it exists but silently fails unless a UDF is registered.

Standard Syntax

-- Varies significantly by engine. -- PostgreSQL: SELECT * FROM tbl WHERE col ~ '^[A-Z]'; -- MySQL: SELECT * FROM tbl WHERE col REGEXP '^[A-Z]';

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Not supported Oracle: Native since 10g SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

REGEXP and RLIKE operators for matching. REGEXP_LIKE(col, pattern [, flags]), REGEXP_SUBSTR(), REGEXP_REPLACE(), REGEXP_INSTR() added in 8.0. Uses ICU regex library (8.0+), POSIX before that. Case-insensitive by default.

SELECT * FROM tbl WHERE col REGEXP '^[A-Z]'; SELECT REGEXP_LIKE('Hello', '^h', 'i'); -- 1 (case-insensitive) SELECT REGEXP_REPLACE('foo123', '[0-9]+', '#'); -- 'foo#' SELECT REGEXP_SUBSTR('foo123bar', '[0-9]+'); -- '123'

PostgreSQL Native syntax

Rich operator set: ~ (match, case-sensitive), ~* (match, case-insensitive), !~ (no match), !~* (no match, case-insensitive). SIMILAR TO for SQL-standard regex. regexp_match(), regexp_matches(), regexp_replace(), regexp_split_to_table() as functions.

SELECT * FROM tbl WHERE col ~ '^[A-Z]'; -- case-sensitive SELECT * FROM tbl WHERE col ~* '^[a-z]'; -- case-insensitive SELECT regexp_match('foo123', '[0-9]+'); -- {123} SELECT regexp_replace('foo123', '[0-9]+', '#'); -- 'foo#' SELECT * FROM regexp_split_to_table('a,b,c', ',') AS t(val);

SQL Server Alternative syntax

No native regex support in T-SQL. LIKE with [] character class provides limited pattern matching. Full regex requires CLR integration (SQLCLR) or application-layer filtering.

-- Limited: LIKE with [] character class: SELECT * FROM tbl WHERE col LIKE '[A-Z]%'; -- starts with a letter SELECT * FROM tbl WHERE col LIKE '[0-9][0-9][0-9]'; -- 3 digits -- Full regex: not supported without CLR

Oracle Native syntax

REGEXP_LIKE(col, pattern [, flags]) for WHERE clauses. Also REGEXP_SUBSTR(), REGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_COUNT(). Uses POSIX Extended Regular Expressions. Case-sensitive by default; use 'i' flag for case-insensitive.

SELECT * FROM tbl WHERE REGEXP_LIKE(col, '^[A-Z]'); SELECT * FROM tbl WHERE REGEXP_LIKE(col, '^[a-z]', 'i'); SELECT REGEXP_REPLACE('foo123', '[0-9]+', '#') FROM DUAL; -- 'foo#' SELECT REGEXP_SUBSTR('foo123bar', '[0-9]+') FROM DUAL; -- '123'

SQLite Alternative syntax

The REGEXP operator exists in the syntax but has no built-in implementation — it requires a user-defined function to be registered at connection time. Most SQLite drivers (Python's sqlite3, etc.) do not register one by default.

-- Requires a registered UDF; not available by default. -- In Python: -- conn.create_function('regexp', 2, lambda p, s: bool(re.search(p, s))) SELECT * FROM tbl WHERE col REGEXP '^[A-Z]';