REGEX_EXTRACT_FIRST()

All functions > STRING > REGEX_EXTRACT_FIRST()

Returns the first substring that matches the pattern, optionally selecting a capture group index (0 = entire match, 1 = first group, …).

Signatures

Returns: The matched substring; empty string when there is no match; NULL if the haystack is NULL

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

Notes

• Only the first match is considered
• Prefer leaving group_number at the default when you need the same capture-group semantics everywhere; some translation paths only support the full-match extract
• See REGEX_EXTRACT_ALL for every non-overlapping match

Aliases

• REGEXP_EXTRACT_FIRST

See also

• String functions

Examples

SELECT
    -- Extract digits
    f1 := REGEX_EXTRACT_FIRST('abc123def', '[0-9]+'),
    -- Extract year
    f2 := REGEX_EXTRACT_FIRST('2024-01-15', '(\d{4})-(\d{2})-(\d{2})', 1),
    -- Extract month
    f3 := REGEX_EXTRACT_FIRST('2024-01-15', '(\d{4})-(\d{2})-(\d{2})', 2),
    -- Extract day
    f4 := REGEX_EXTRACT_FIRST('2024-01-15', '(\d{4})-(\d{2})-(\d{2})', 3),
    -- No match returns empty
    f5 := REGEX_EXTRACT_FIRST('no match here', '[0-9]+'),
    -- NULL haystack
    f6 := REGEX_EXTRACT_FIRST(NULL(VARCHAR), '[0-9]+')
;