How to Use Regex in BigQuery
Last updated August 26, 2026 · By the SaturnSQL team
REGEXP_CONTAINS tests whether a string matches a pattern, REGEXP_EXTRACT pulls out the first matching group, and REGEXP_REPLACE substitutes matches with new text.
SELECT REGEXP_CONTAINS(email, r'^[\w.+-]+@[\w-]+\.[a-z]{2,}$') AS is_valid_email
FROM users;Extracting a capture group
SELECT url, REGEXP_EXTRACT(url, r'/blog/([a-z0-9-]+)') AS slug
FROM events;Replacing matches
SELECT REGEXP_REPLACE(phone, r'[^0-9]', '') AS digits_only
FROM users;Use a raw string literal (prefixed with r) for regex patterns so backslashes do not need double escaping.
Always write the pattern as a raw string
Without the r prefix the SQL parser consumes the backslashes before the regex engine sees them, so r'\d+' works while '\d+' either fails to parse or silently matches something else. Prefixing every pattern with r is the habit that avoids the whole class of problem.
SELECT REGEXP_CONTAINS(email, r'^[a-z.]+@acme\.(io|dev)$') FROM customers;REGEXP_EXTRACT wants exactly one capturing group
The function returns the first capturing group, so a pattern with two groups is rejected and a pattern with none returns the whole match. When you need grouping for alternation without capturing it, use a non-capturing group, (?:...). REGEXP_EXTRACT_ALL returns an array when a row can match more than once.
SELECT REGEXP_EXTRACT(url, r'utm_(?:source|medium)=([^&]+)') FROM sessions;Regex is the slow option on a large scan
BigQuery bills for bytes scanned regardless, but regex still costs wall-clock time compared with the simpler string functions, and it is easy to reach for out of habit. LIKE, STARTS_WITH, ENDS_WITH and CONTAINS_SUBSTR cover most real filters and read better in review. Keep regex for the cases that genuinely need a pattern, and put the cheap predicate first so it filters before the expensive one runs.
SELECT * FROM analytics.sessions
WHERE STARTS_WITH(url, 'https://saturnsql.com/learn')
AND REGEXP_CONTAINS(url, r'utm_source=([^&]+)');Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it freeDo more with BigQuery
Switching SQL clients?
Side-by-side roundups with prices checked against each vendor.
