- Categories:
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> ] )
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
wheren
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 | +----------------+
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 |
+------------------------------------------+
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 |
+----------------------------------------------------+
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 |
+---------------------------------------------------------------------------------+