REGEX_EXTRACT_ALL()

All functions > STRING > REGEX_EXTRACT_ALL()

Returns an array of every non-overlapping substring that matches the pattern, in left-to-right order, optionally per capture group.

Signatures

Returns: Array of all matching substrings; empty array when there are no matches; NULL if the haystack is NULL

REGEX_EXTRACT_ALL(string: VARCHAR, pattern: VARCHAR, [group_number: BIGINT]) → ARRAYVARCHAR

Notes

• Group 0 (default) is the entire match for each occurrence; group 1+ is that capture group per occurrence
• Not available on every deployment target (unsupported targets fail translation)
• Prefer the default group_number when you need portable capture-group behavior

See also

• String functions

Examples

SELECT
    f1 := REGEX_EXTRACT_ALL('abc123def456', '[0-9]+'), -- Extract all digit groups
    f2 := REGEX_EXTRACT_ALL('cat bat hat', '[a-z]at'), -- Extract all matches
    f3 := REGEX_EXTRACT_ALL('a1b2c3', '([a-z])([0-9])', 1), -- Extract first capture group
    f4 := REGEX_EXTRACT_ALL('no match', '[0-9]+'), -- No matches returns empty array
    f5 := REGEX_EXTRACT_ALL(NULL(VARCHAR), '[0-9]+') -- NULL haystack
;