Oracle / PLSQL: REGEXP_REPLACE Function

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

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

Applies To

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

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 ('TechOnTheNet is a great resource', '^(\S*)', 'CheckYourMath')
FROM dual;
Result: 'CheckYourMath 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;
Result: '#, #, 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;
Result: '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 ('Anderson', 'a|e|i|o|u', 'G')
FROM dual;
Result: '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 'Anderson' will not be matched.

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

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;
Result: 'GndGrsGn'

Now because we have provide a match_parameter of 'i', the query will replace 'A' in the string. This time, the 'A' in 'Anderson' 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 ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 1, 'i')
FROM dual;
Result: 'TZchOnTheNet'

This example will replace the second character ('e') in 'TechOnTheNet' 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 ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 2, 'i')
FROM dual;
Result: 'TechZnTheNet'

This example will replace the fifth character ('O') in 'TechOnTheNet' 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 ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 3, 'i')
FROM dual;
Result: 'TechOnThZNet'

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

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .