regexp_extract_all function

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

Extracts all of the strings in str that match the regexp expression and correspond 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<STRING>.

The regexp string must be a Java regular expression.

When using literals, use `raw-literal` (`r` prefix) to avoid escape character pre-processing.

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]