REGEXQuery 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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 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 ✓ Supported 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 ✓ Supported 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: Since all PostgreSQL: Since all SQL Server: Not supported Oracle: Since 10g SQLite: Not supported

Per-Database Syntax & Notes

MySQL

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

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

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

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

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]';