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)