Oracle / PLSQL: REGEXP_REPLACE Function

admin

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 ('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:
contact_id         last_name
1000         Anderson
2000         Smith
3000         Johnson

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:
contact_id         last_name         New Name
1000         Anderson         GndGrsGn
2000         Smith         SmGth
3000         Johnson         JGhnsGn
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.