Regex in SQL cheat sheet

Match, extract, and replace with regular expressions across Snowflake, BigQuery, Postgres, and DuckDB — with the dialect quirks.

SQL & DatabasesIntermediate6 sections

Match / filter rows

where regexp_like(email, '^[^@]+@[^@]+\\.[a-z]{2,}$') -- Snowflake: Snowflake anchors the pattern to the FULL string implicitly in REGEXP_LIKE — ^ and $ are redundant but harmless.
where regexp_contains(email, r'@gmail\.com$') -- BigQuery: BigQuery uses RE2 and raw strings (r'...') so you skip double-escaping. RE2 has no backreferences or lookaheads.
where email ~* 'gmail\.com$' -- Postgres: ~ is case-sensitive match, ~* case-insensitive, !~ negation. Substring semantics: anchor explicitly.
where regexp_matches(email, 'gmail') -- DuckDB: DuckDB's regexp_matches does partial matching; regexp_full_match requires the whole string to match.

regexp_substr(url, 'utm_source=([^&]+)', 1, 1, 'e', 1) -- Snowflake: The 'e' flag plus trailing group number returns the capture group. The argument order trips everyone: subject, pattern, position, occurrence, params, group.
regexp_extract(url, r'utm_source=([^&]+)') -- BigQuery: Returns the first capture group automatically if one exists. regexp_extract_all returns an array of every match.
(regexp_match(url, 'utm_source=([^&]+)'))[1] -- Postgres: regexp_match returns a text array of groups; index it. substring(url from 'pattern') is the terse one-group form.
regexp_extract(url, 'utm_source=([^&]+)', 1) -- DuckDB: Third argument selects the group; 0 means the whole match.

regexp_replace(phone, '[^0-9]', '') -- everywhere: Strip non-digits — same signature in all four engines. The workhorse of data cleaning.
regexp_replace(s, '(\\d{3})(\\d{4})', '\\1-\\2') -- Snowflake/Postgres: Backreferences in the replacement are \\1 in Snowflake and Postgres, but BigQuery RE2 also uses \\1 while DuckDB accepts \\1.
regexp_replace(s, 'a+', 'X', 'g') -- Postgres needs the g flag: Postgres replaces only the FIRST match unless you pass 'g'. Snowflake, BigQuery, and DuckDB replace all by default.

split_part(path, '/', 3): Not regex, but solves most extraction jobs faster — supported in Snowflake, Postgres, and DuckDB. BigQuery uses split(path, '/')[safe_offset(2)].
regexp_split_to_table(csv_line, ',\\s*') -- Postgres: Split straight to rows. Snowflake pairs split() with lateral flatten; DuckDB uses unnest(string_split_regex(...)).
regexp_count(body, '\\berror\\b') -- Snowflake/Postgres 15+/DuckDB: Count matches per row. BigQuery equivalent is array_length(regexp_extract_all(body, r'\berror\b')).

regexp_replace(trim(name), '\\s+', ' '): Collapse internal whitespace runs after trimming — the standard name normalizer.
where not regexp_like(id, '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'): Reject malformed UUIDs at the staging boundary rather than letting them poison joins.
regexp_extract(user_agent, r'Chrome/(\d+)') -- BigQuery: Version sniffing from user agents; extract the major version only and cast to INT64.
try_cast(regexp_substr(raw, '-?\\d+\\.?\\d*') as double) -- Snowflake: Pull the first numeric token out of messy text, and let try_cast absorb the failures as NULLs.

'\\d' vs r'\d': Snowflake and Postgres need doubled backslashes inside normal string literals; BigQuery raw strings and DuckDB single-quoted strings do not.
RE2 vs PCRE-ish: BigQuery (RE2) and DuckDB (RE2) lack lookarounds and backreferences in patterns. Snowflake (POSIX ERE) also lacks lookarounds. Design patterns to avoid them.
regexp_like(col, pattern, 'i'): Case-insensitivity is a flag argument in Snowflake, (?i) inline in BigQuery/DuckDB, and ~* in Postgres. Comment which one you meant.

From DataLane — tutorials at/blog, practice SQL live in theplayground.