Skip to main content

Categories: String, Regular Expressions

REGEXP_EXTRACT

Extracts the first string in expression that matches the REGEXP expression and corresponds to the REGEX group index.

Syntax​

REGEXP_EXTRACT(input string, regex string [, expr_group_index integer]) → string​

  • input: The expression to search for a matching string.
  • regex: The Perl-compatible regular expression (PCRE) to match against.
  • expr_group_index (optional): A regular expression group number, defining which portion of the matching string will be returned.

Examples

REGEXP_EXTRACT example 
SELECT REGEXP_EXTRACT('100-500', '(\d+)-(\d+)', 1)
-- 100
REGEXP_EXTRACT example 
SELECT REGEXP_EXTRACT('100-500', '(\d+)-(\d+)', 0)
-- 100-500
REGEXP_EXTRACT example 
SELECT REGEXP_EXTRACT('user@example.com', '([a-zA-Z0-9]+)@([a-zA-Z0-9.]+)', 2)
-- example.com
REGEXP_EXTRACT example 
SELECT REGEXP_EXTRACT('(555) 123-4567', '(\d{3})-(\d{4})', 2)
-- 4567