regexp_extract() function
The regexp_extract() function returns the matching text item. This function is analogous to the REGEXP_SUBSTR() function that some vendors provide.
Syntax
varchar = regexp_extract(varchar input, varchar pattern [, int
start_pos [, int reference]] [, varchar flags]);
nvarchar = regexp_extract(nvarchar input, nvarchar pattern [, int
start_pos [, int reference]] [, 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 search. The default
is 1.
The reference value
specifies which instance of
the pattern to extract. The default is 1.
For a description of the flags value, see flags argument.
Returns
The function returns the matching text item.Example
select regexp_extract('hello to you', '.o',1,1);
REGEXP_EXTRACT
----------------
lo
(1 row)
select regexp_extract('hello to you', '.o',1,2);
REGEXP_EXTRACT
----------------
to
(1 row)
select regexp_extract('hello to you', '.o',1,3);
REGEXP_EXTRACT
----------------
yo
(1 row)