How to Use REGEXP_EXTRACT in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
REGEXP_EXTRACT returns the first capture group and REGEXP_EXTRACT_ALL returns every match as an array. BigQuery runs RE2, so lookahead is not supported.
With no parentheses in the pattern, REGEXP_EXTRACT returns the whole match. With exactly one capture group it returns that group, which is the usual way to pull a fragment out of a larger string. More than one group is an error, so use non-capturing (?:...) groups for the parts you only need for matching.
SELECT REGEXP_EXTRACT(url, r'/blog/([a-z0-9-]+)') AS slug
FROM page_views;Every match, not just the first
REGEXP_EXTRACT_ALL returns an ARRAY<STRING> of all matches, which you can then UNNEST into rows.
SELECT tag
FROM articles, UNNEST(REGEXP_EXTRACT_ALL(body, r'#(\w+)')) AS tag;No lookahead or lookbehind
BigQuery uses the RE2 engine, which guarantees linear-time matching by refusing constructs that need backtracking. That rules out lookahead (?=...), lookbehind (?<=...), and backreferences inside the pattern. Rewrite the condition as an explicit capture group, or split the logic across a REGEXP_CONTAINS filter and a separate extract.
-- Instead of (?<=user_)\d+ , capture what follows the prefix
SELECT REGEXP_EXTRACT(ref, r'user_(\d+)') AS user_id
FROM events;No match returns NULL
A row that does not match yields NULL rather than an empty string, so wrap the call in IFNULL when a downstream column is NOT NULL, and remember that NULL propagates through concatenation.
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 free