SQL Regex

-- ===== Postgres (POSIX) =====
SELECT 'hello world' ~ '^hello';              -- TRUE (case-sensitive match)
SELECT 'HELLO' ~* 'hello';                     -- TRUE (case-INsensitive)
SELECT 'foo' !~ 'bar';                          -- TRUE (no match)
SELECT 'foo' !~* 'BAR';                         -- TRUE (case-insensitive no match)

-- Capture + extract:
SELECT regexp_match('order #42 total $49.95', '#(\d+) total \$(\d+\.\d{2})');
-- {42, 49.95}

-- All matches:
SELECT regexp_matches('a 1 b 22 c 333', '(\d+)', 'g');

-- Replace:
SELECT regexp_replace('hello WORLD', 'world', 'web', 'i');
-- 'hello web'

-- Split:
SELECT regexp_split_to_array('a,b,,c', ',');
SELECT regexp_split_to_table('a,b,,c', ',');

-- ===== SIMILAR TO (SQL-99) =====
SELECT 'abc' SIMILAR TO 'a%';                   -- TRUE (% is wildcard)
-- More limited than POSIX; rarely worth it; prefer ~ / ~*.

-- ===== Indexing regex queries =====
-- POSIX regex does NOT use a normal btree index.
-- Options:
--   1. trigram index for substring + similarity searches:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);
-- Now:
SELECT * FROM users WHERE name ILIKE '%alex%';   -- uses the trigram index

--   2. functional index on a derived value:
CREATE INDEX users_email_domain ON users ((split_part(email, '@', 2)));
SELECT * FROM users WHERE split_part(email, '@', 2) = 'example.com';

-- ===== MySQL =====
SELECT 'hello world' REGEXP '^hello';     -- 1
SELECT 'HELLO' REGEXP '^hello';            -- 1 in case-INsensitive collation, 0 in case-sensitive

-- Case sensitivity follows the COLLATION; use BINARY for strict:
SELECT BINARY 'HELLO' REGEXP '^hello';     -- 0

-- Extract:
SELECT REGEXP_SUBSTR('order #42 total $49.95', '#([0-9]+)', 1, 1, '', 1);

-- Replace:
SELECT REGEXP_REPLACE('hello WORLD', 'WORLD', 'web');

-- ===== SQLite =====
-- SQLite has no built-in regex; load the regexp extension or use the LIKE / GLOB operators.

-- ===== Common patterns =====
-- Email-ish validation in Postgres:
ALTER TABLE users ADD CONSTRAINT email_shape CHECK (email ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$');

-- Slug validation:
CHECK (slug ~ '^[a-z0-9-]+$')

-- ISO date filter:
SELECT * FROM logs WHERE meta::text ~ '\d{4}-\d{2}-\d{2}';

-- ===== Performance traps =====
-- - Regex in WHERE on big tables without an index -> seq scan
-- - Leading wildcard (^.*x) defeats indexing
-- - Backreferences + lookarounds are slow; POSIX usually doesn't support them
-- - User-supplied patterns risk ReDoS even at the DB

-- ===== Patterns to internalise =====
-- - ILIKE + pg_trgm GIN index for substring search in Postgres
-- - regexp_replace / regexp_match for one-off cleaning
-- - Anchored regex (^ + $) for validation in CHECK constraints
-- - Move heavy regex to the app where you can index the input

-- ===== Pitfalls =====
-- - Forgetting that SQL regex is double-escaped in many drivers
-- - Case sensitivity differing across collations / databases
-- - Trigger-heavy regex validation on hot inserts -> slow
-- - Regex 'just to validate' an email — RFC 5321 is too rich; send a verification email

-- Postgres regex match
SELECT * FROM logs WHERE message ~ '^ERROR.*';
-- MySQL
SELECT * FROM logs WHERE message REGEXP '^ERROR.*';