regexp_extract_all_sp() function

The regexp_extract_all_sp() function processes a regular expression against varchar or nvarchar input. All subpatterns are returned in an array with the first element (element 0) corresponding to the full match.

Syntax

The regexp_extract_all_sp() function has the following syntax:

array(varchar) = regexp_extract_all_sp(varchar input, varchar pattern 
[, int start_pos][, varchar flags]);

array(nvarchar) = regexp_extract_all_sp(nvarchar input, nvarchar pattern 
[, int start_pos][, varchar flags]);

The input value specifies the varchar or nvarchar value against which the regular expression is processed.

The pattern value specifies the regular expression. For a description of how to specify Perl compatible regular expression (PCRE) patterns for Unicode data, see any general PCRE documentation or web sources.

The start_pos value specifies the character position at which to start the extract. The default is 1.

For a description of the flags value, see flags argument.

Returns

The function returns all subpatterns in an array with the first element (element 0) corresponding to the full match.

Example

select array_combine(regexp_extract_all_sp('Robert Szissel, 128 Folson 
St, Boston', 
'([^,]*),[[:space:][:digit:]]*([^[:space:]]*).*,[[:space:]]*(.*)'),'|'
);
                           ARRAY_COMBINE
--------------------------------------------------------------------
 Robert Szissel, 128 Folson St, Boston|Robert Szissel|Folson|Boston
(1 row)