regexp_replace_sp() function
The regexp_replace_sp() function processes a regular expression against varchar or nvarchar input and replaces each instance of a subpattern with the values in an array.
Syntax
The regexp_replace_sp() function has the following
syntax:
varchar = regexp_replace_sp(varchar input, varchar pattern, array
replacements [, int start_pos] [, varchar flags]);
nvarchar = regexp_replace_sp(nvarchar input, nvarchar pattern, array
replacements [, 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 replacement
array specifies
the values to substitute for
each instance of the subpattern.
The start_pos
value specifies
the character position at which
to start the replacement. The default is 1.
For a description of the flags
value, see flags argument.
Returns
The function replaces each instance of the subpattern with the values in the array.Example
select regexp_replace_sp('Robert Szissel, 128 Folson St, Boston',
'([[:digit:]]+)[^.]*,.*(Boston)', array_split('37000,Cleveland',
','));
REGEXP_REPLACE_SP
--------------------------------------------
Robert Szissel, 37000 Folson St, Cleveland
(1 row)