regex_replace function

The regex_replace function replaces a string that matches a regular expression.

Syntax

regex_replace(
     str     lvarchar,
     re      lvarchar,
     rep     lvarchar,
     limit   integer DEFAULT 0,
     copts   integer DEFAULT 1) 
returns lvarchar
regex_replace(
     str     clob,
     re      lvarchar,
     rep     lvarchar,
     limit   integer DEFAULT 0,
     copts_string   lvarchar)
returns clob

Parameters

str
The string to search. Can be of type CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, or CLOB. A null value is treated as an empty string.
re
The regular expression. Can be of type CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR. Cannot be null.
rep
The string to replace. Can be of type CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR. See topic on Metacharacters for metacharacter handling. Cannot be null.
limit (Optional)
0 = Default. All matches are returned.
Positive integer = The maximum number of matches to return.
copts (Optional)
The type of regex search:
  • 0 = Basic regex
  • 1 = Default. Extended POSIX regex
  • 2 = Basic regex and ignore case
  • 3 = Extended POSIX regex and ignore case
copts_string (Optional)
The type of regex search:
  • basic = Basic regex
  • extended = Default. Extended POSIX regex
  • basic,icase = Basic regex and ignore case
  • extended,icase = Extended POSIX regex and ignore case
  • basic,rtrim = Basic regex with rtrim
  • extended,rtrim = Extended POSIX regex with rtrim
  • basic,icase,rtrim = Basic regex and ignore case with rtrim
  • extended,icase,rtrim = Extended POSIX regex and ignore case with rtrim

Description

Use the regex_replace function to replace text in a string. You can run the regex_replace function in an EXECUTE FUNCTION statement or in an SQL query, such as a SELECT statement.

Returns

A single value that is the input string with all substrings, up to the limit value, that match the input regular expression pattern replaced as specified by the replacement pattern.

An exception is returned if an error occurred.

Example

In this example, you want to have a web-based search engine that returns search matches in bold using the "and" HTML tags. Furthermore, you want to make the entire word bold in which the match was found. The regular expression in the example below looks for a word in which "she" or "She" occurs, then replaces the matched text with itself (&), enclosed by "and" HTML tags:
execute function regex_replace (
     'She sells seashells on the seashore. The seashells she sells are seashore 
           seashells.',
     '( |^)[A-Za-z]*[Ss]he[a-z]*[.,$]',
     '<b>&</b>');
(expression) <b>She </b>sells<b> seashells </b>on the seashore. The<b>
seashells </b><b>she </b>sells are seashore<b> seashells.</b>
The result displayed on a web page looks like this:
She sells seashells on the seashore. The seashells she sells are seashore
seashells.
You can restrict the number of matches replaced by using the optional integer argument:
execute function regex_replace(
     'She sells seashells on the seashore. The seashells she sells are
     seashore seashells.',
     '( |^)[A-Za-z]*[Ss]he[a-z]*[ .,$]',
     '<b>&</b>',
       3);
The result displayed on a web page looks like this, with only three replacements:
She sells seashells on the seashore. The seashells she sells are seashore
seashells.
The following example runs the regex_replace function in a SELECT statement:
select id,
        regex_replace(twister, '( |^)[A-Za-z]*[Ss]he[a-z]*[ .,$]',
        '<b>&</b>')
from    tongue_twisters
where   regex_match(twister, '[Ss]he');
id            246
(expression)  Sally sells sea<b> shells </b>by the sea shore. But
              if Sally sells sea shells by the sea shore then
              where are the sea<b> shells </b>Sally sells?
id            306
(expression)  I slit a<b> sheet,</b> a<b> sheet </b>I slit, and
              on that slitted <b> sheet </b>I sit.
id            361
(expression)  <b>She </b>sells<b> seashells </b>on the seashore.
              The<b> seashells </b>she sells are seashore
              <b> seashells.</b>
The following statement reference four subgroups within the matched text:
execute function regex_replace (
        'swap me all around',
        '(.*) (.*) (.*) (.*)',
        '\4 \3 \2 \1'
);
(expression) around all me swap
1 row(s) retrieved.
execute function regex_replace ('swap me', '(.*) (.*)', '&: \2 \1');
(expression) swap me: me swap
1 row(s) retrieved.