REGEXP_REPLACE¶

Syntax¶

REGEXP_REPLACE( <subject> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] )

Arguments¶

Required:

subject

The string to search for matches.

pattern

Pattern to match.

For guidelines on specifying patterns, see String functions (regular expressions).

Optional:

replacement

String that replaces the substrings matched by the pattern. If an empty string is specified, the function removes all matched patterns and returns the resulting string.

Default: '' (empty string).

position

Number of characters from the beginning of the string where the function starts searching for matches.

Default: 1 (the search for a match starts at the first character on the left)

occurrence

Specifies which occurrence of the pattern to replace. If 0 is specified, all occurrences are replaced.

Default: 0 (all occurrences)

parameters

String of one or more characters that specifies the parameters used for searching for matches. Supported values:

Parameter	Description
c	Case-sensitive matching
i	Case-insensitive matching
m	Multi-line mode
e	Extract submatches
s	POSIX wildcard character . matches \n

Default: c

For more details, see Specifying the parameters for the regular expression.

Returns¶

Returns a value of type VARCHAR.

If no matches are found, returns the original subject.

Returns NULL if any argument is NULL.

Usage notes¶

• The replacement string can contain backreferences to capture groups (for example, sub-expressions of the pattern). A capture group is a regular expression that is enclosed within parentheses (( )). The maximum number of capture groups is 9.

  Backreferences match expressions inside a capture group. Backreferences have the form n where n is a value from 0 to 9, inclusive, which refers to the matching instance of the capture group. For more information, see Examples (in this topic).

• Parentheses (( )) and square brackets ([ ]) currently must be double-escaped to parse them as literal strings.

  The example below shows how to remove parentheses:

  SELECT REGEXP_REPLACE('Customers - (NY)','\\(|\\)','') AS customers;
  

  Copy

  +----------------+
  | CUSTOMERS      |
  |----------------|
  | Customers - NY |
  +----------------+
  

• For additional usage notes, see the General usage notes for regular expression functions.

Collation details¶

Arguments with collation specifications are currently not supported.

Examples¶

The following example replaces all spaces in the string with nothing (that is, all spaces are removed):

SELECT REGEXP_REPLACE('It was the best of times, it was the worst of times',
                      '( ){1,}',
                      '') AS result;

+------------------------------------------+
| RESULT                                   |
|------------------------------------------|
| Itwasthebestoftimes,itwastheworstoftimes |
+------------------------------------------+

The following example matches the string times and replaces it with the string days. Matching begins at the first character in the string and replaces the second occurrence of the substring:

SELECT REGEXP_REPLACE('It was the best of times, it was the worst of times',
                      'times',
                      'days',
                      1,
                      2) AS result;

+----------------------------------------------------+
| RESULT                                             |
|----------------------------------------------------|
| It was the best of times, it was the worst of days |
+----------------------------------------------------+

The following example uses backreferences to rearrange the string firstname middlename lastname as lastname, firstname middlename and insert a comma between lastname and firstname:

SELECT REGEXP_REPLACE('firstname middlename lastname',
                      '(.*) (.*) (.*)',
                      '\\3, \\1 \\2') AS name_sort;

+--------------------------------+
| NAME_SORT                      |
|--------------------------------|
| lastname, firstname middlename |
+--------------------------------+