regexp_extract_sp() function
The regexp_extract_sp() function processes a regular expression against varchar or nvarchar input, returning the specified subpattern.
Syntax
The regexp_extract_sp() function has the following
syntax:
varchar = regexp_extract_sp(varchar input, varchar pattern, int
start_pos, int reference[, varchar flags]);
nvarchar = regexp_extract_sp(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 extract. The default is 1.
The reference value specifies
which instance of the pattern
to extract.
For a description of the flags value, see flags argument.
Returns
The function returns the specified subpattern.Example
Consider the following
table:
create table sample(col1 varchar(20));
CREATE TABLE
insert into sample values('bcaaabc');
INSERT 0 1
insert into sample values('abcbc');
INSERT 0 1
insert into sample values('bbb');
INSERT 0 1
insert into sample values('bcd');
INSERT 0 1
insert into sample values('bccdebc');
INSERT 0 1
insert into sample values('def');
INSERT 0 1
insert into sample values('efgbcbc');
INSERT 0 1
The following query extracts the character a, c, or f, whichever
is found first, from each value
of the
table:
select regexp_extract_sp ( col1, '[acf]' ,1,1)from sample order by
rowid;
REGEXP_EXTRACT_SP
-------------------
c
a
c
c
f
f
(7 rows)