regexp_extract_all function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Extracts the all strings in str that matches the regexp expression and corresponds to the regex group index.

Syntax 

regexp_extract_all(str, regexp [, idx] )

Arguments

  • str: A STRING expression to be matched.

  • regexp: A STRING expression with a matching pattern.

  • idx: An optional integral number expression greater or equal 0 with default 1.

Returns

An ARRAY of STRING.

The regexp string must be a Java regular expression. String literals are unescaped. For example, to match '\abc', a regular expression for regexp can be '^\\abc$'. regexp may contain multiple groups. idx indicates which regex group to extract. An idx of 0 means match the entire regular expression.

Examples 

> SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
 [100, 300]