Categories:

String Functions (Regular Expressions)

REGEXP_REPLACE

Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject.

See also String Functions (Regular Expressions).

Syntax

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

Arguments

Required:

subject

Subject to match.

pattern

Pattern to match.

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:

c , i , m , e , s

For more details, see regular expression parameters.

Default: c

Usage Notes

  • The replacement string can contain backreferences to capture groups (i.e. 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 details, 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;
    
    +----------------+
    | CUSTOMERS      |
    |----------------|
    | Customers - NY |
    +----------------+
    
    Copy
  • 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 (i.e. all spaces are removed):

select regexp_replace('It was the best of times, it was the worst of times', '( ){1,}','') as "result" from dual;

+------------------------------------------+
| result                                   |
|------------------------------------------|
| Itwasthebestoftimes,itwastheworstoftimes |
+------------------------------------------+
Copy

The following example matches the string times and replaces it with the string days. Matching begins at the 1st 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" from dual;

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

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" from dual;

+---------------------------------------------------------------------------------+
| REGEXP_REPLACE('FIRSTNAME MIDDLENAME LASTNAME','(.*) (.*) (.*)','\\3, \\1 \\2') |
|---------------------------------------------------------------------------------|
| lastname, firstname middlename                                                  |
+---------------------------------------------------------------------------------+
Copy