Link Search Menu Expand Document

EXTRACT_ALL

Extracts fragments within a string that match a specified regex pattern. String fragments that match are returned as an array of TEXT types.

Syntax

EXTRACT_ALL( <expr>, '<regex_pattern>' )
Parameter Description
<expr> Any expression that evaluates to a TEXT data type
<regex_pattern> An re2 regular expression used for matching.

Example

In the example below, EXTRACT_ALL is used to match variants of “Hello World”. The regular expression pattern 'Hello.[Ww]orld!?' does not match any special characters except for !.

SELECT
	EXTRACT_ALL (
		'Hello world, ;-+ Hello World!',
		'Hello.[Ww]orld!?'
	);

Returns:

["Hello world","Hello World!"]