Oracle / PLSQL: REGEXP_REPLACE Function

This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_REPLACE function with syntax and examples.

Description

The Oracle/PLSQL REGEXP_REPLACE function is an extension of the REPLACE function. This function, introduced in Oracle 10g, will allow you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching.

Syntax

The syntax for the REGEXP_REPLACE function in Oracle is:

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Parameters or Arguments

string

The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

pattern

The regular expression matching information. It can be a combination of the following:

Value	Description
^	Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$	Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
|	Used like an "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]	Matches one collation element that can be more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
\A	Matches the beginning of a string or matches at the end of a string before a newline character.
\Z	Matches at the end of a string.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.

replacement_string

Optional. Matched patterns will be replaced with replacement_string in string. If the replacement_string parameter is omitted, the function simply removes all matched patterns, and returns the resulting string.

start_position

Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.

nth_appearance

Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string. If you specify 0 for this parameter, all appearances of pattern will be replaced in string.

match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_REPLACE function. It can be a combination of the following:

Value	Description
'c'	Perform case-sensitive matching.
'i'	Perform case-insensitive matching.
'n'	Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm'	expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
'x'	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

Returns

The REGEXP_REPLACE function returns a string value.

Note

• If there are conflicting values provided for match_parameter, the REGEXP_REPLACE function will use the last value.
• See also the REPLACE function.

Applies To

The REGEXP_REPLACE function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g

Example - Match on First Word

Let's start by using the REGEXP_REPLACE function to replace the first word in a string.

For example:

SELECT REGEXP_REPLACE ('aodba is a great resource', '^(\S*)', 'mySite')
FROM dual;

Output: 'mySite is a great resource'

This example will return 'CheckYourMath is a great resource' because it will start the match at the beginning of the string as specified by ^ and then find the first word as specified by (\S*). The function will then replace this first word with 'CheckYourMath'.

Example - Match on Digit Characters

Let's look next at how we would use the REGEXP_REPLACE function to match on a single digit character pattern.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '\d', '#')
FROM dual;

Output: '#, #, and ## are numbers in this example'

This example will replace all numeric digits in the string as specified by \d. It will replace the occurrences with a # character.

We could change our pattern to search for only two-digit numbers.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(\d)(\d)', '#')
FROM dual;

Output: '2, 5, and # are numbers in this example'

This example will replace a number that has two digits side-by-side as specified by (\d)(\d). In this case, it will skip over the 2 and 5 numeric values and replace 10 with a # character.

Now, let's look how we would use the REGEXP_REPLACE function with a table column to replace two digit numbers.

For example:

SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'TBD')
FROM contacts;

In this example, we are going to replace all two-digit values from the address field in the contacts table with the value 'TBD'.

Example - Match on more than one alternative

The next example that we will look at involves using the | pattern. The | pattern is used like an "OR" to specify more than one alternative.

For example:

SELECT REGEXP_REPLACE ('James', 'a|e|i|o|u', 'G')
FROM dual;

Output: 'AndGrsGn'

This example will return 'AndGrsGn' because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_REPLACE function will perform a case-sensitive search which means that the 'A' in 'James' will not be matched.

We could modify our query as follows to perform a case-insensitive search as follows:

SELECT REGEXP_REPLACE ('James', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;

Output: 'GndGrsGn'

Now because we have provide a match_parameter of 'i', the query will replace 'A' in the string. This time, the 'A' in 'James' will be found as a match. Notice also that we specified 0 as the 5th parameter so that all occurrences would be replaced.

Now, let's quickly show how you would use this function with a column.

So let's say we have a contact table with the following data:

Now, let's run the following query:

SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', 'G', 1, 0, 'i') AS "New Name"
FROM contacts;

These are the results that would be returned by the query:

Example - Match on nth_occurrence

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to replace in the string.

First Occurrence

Let's look at how to replace the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('aodba', 'a|e|i|o|u', 'Z', 1, 1, 'i')
FROM dual;

Output: 'Zodba'

This example will replace the second character ('e') in 'aodba' because it is replacing the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('aodba', 'a|e|i|o|u', 'Z', 1, 2, 'i')
FROM dual;

Output: 'aZdba'

This example will replace the fifth character ('O') in 'aodba' because it is replacing the second occurrence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:

SELECT REGEXP_REPLACE ('aodba', 'a|e|i|o|u', 'Z', 1, 3, 'i')
FROM dual;

Output: 'aodbZ'

This example will replace the ninth character ('e') in 'aodba' because it is replacing the third occurrence of a vowel (a, e, i, o, or u) in the string.