In Oracle, the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern.

It extends the functionality of the REPLACE() function by allowing us to use regular expression patterns.

Syntax

The syntax goes like this:

REGEXP_REPLACE ( source_char, pattern
                 [, replace_string
                    [, position
                       [, occurrence
                          [, match_param ]
                       ]
                    ]
                 ]
               )

Where:

• source_char is a character expression that serves as the search value.
• pattern is the regular expression.
• replace_string is the replacement string.
• position is a positive integer that specifies where to begin the search. The default is 1, meaning, start the search at the first character.
• occurrence is a nonnegative integer that specifies which occurrence to replace. The default is 0, which means replace all occurrences.
• match_param lets you change the default matching behaviour of the function. For example, it allows you to specify case-sensitivity, how multiple lines and spaces are dealt with, etc. This argument works the same as when used with the REGEXP_COUNT() function. See Oracle’s documentation for more information.

Example

Here’s a basic example of using REGEXP_REPLACE() in Oracle:

SELECT 
    REGEXP_REPLACE('Cats and dogs', 'd.g', 'bird')
FROM DUAL;

Result:

Cats and birds

In this case there’s a match, and the substring is replaced with the replacement string.

Regular expressions can be very powerful, and this example uses a very simple example. In order to use REGEXP_REPLACE() effectively, you’ll need to know the correct pattern to use for the desired outcome.

No Match

Here’s an example where there’s no match:

SELECT REGEXP_REPLACE('Cats and dogs', 't.g', 'bird');

Result:

+------------------------------------------------+
| REGEXP_REPLACE('Cats and dogs', 't.g', 'bird') |
+------------------------------------------------+
| Cats and dogs                                  |
+------------------------------------------------+

There’s no match, so the original string is returned unchanged.

Multiple Matches

Here’s an example with multiple matches:

SELECT 
    REGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird')
FROM DUAL;

Result:

My bird likes other birds

However, you can specify which occurrence to replace if required:

SELECT 
REGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 1, 2)
FROM DUAL;

Result:

My dog likes other birds

Note that I added two arguments here – 1 and 2. The 1 specifies whereabouts in the string to start the search (in this case, at the first character). The 2 is what specifies which occurrence to replace. In this case, the second occurrence is replaced.

Here’s what happens if I start the search after the first occurrence:

SELECT 
REGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 7, 2)
FROM DUAL;

Result:

My dog likes other dogs

In this case the string isn’t updated, because there’s only one more occurrence after the starting position.

If I change the last argument to 1, then it’s updated as specified (because it’s the first occurrence after the specified starting position):

SELECT 
REGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 7, 1)
FROM DUAL;

Result:

My dog likes other birds

And in case you’re wondering, 0 specifies all occurrences:

SELECT REGEXP_REPLACE(
    'My dog likes big dogs and small dogs', 
    'd.g', 
    'bird', 1, 0
    )
FROM DUAL;

Result:

My bird likes big birds and small birds

But it still respects any starting position that’s been specified:

SELECT REGEXP_REPLACE(
    'My dog likes big dogs and small dogs', 
    'd.g', 
    'bird', 7, 0
    )
FROM DUAL;

Result:

My dog likes big birds and small birds

Case Sensitivity

The REGEXP_REPLACE() function follows Oracle’s collation determination and derivation rules, which define the collation to use when matching the string with the pattern.

However, you can explicitly specify case-sensitivity with the optional sixth argument. When you do this, it overrides any case-sensitivity or accent-sensitivity of the determined collation.

You can specify i for case-insensitive matching and c for case-sensitive matching.

Here’s an example:

SELECT 
    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0) AS "Default",
    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0, 'i') AS "Case Insensitive",
    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0, 'c') AS "Case Sensitive"
FROM DUAL;

Result:

   Default    Case Insensitive    Case Sensitive 
__________ ___________________ _________________ 
My Cats    My dogs             My Cats         

It appears from these results that my default collation is case-sensitive. The other two strings were forced to a case-insensitive and case-sensitive matching respectively.

Null Arguments

Passing null results in null for most arguments, except for the second and sixth arguments:

SET NULL 'null';
SELECT 
    REGEXP_REPLACE(null, 'c.t', 'dog', 1, 0, 'i') AS "1",
    REGEXP_REPLACE('Cat', null, 'dog', 1, 0, 'i') AS "2",
    REGEXP_REPLACE('Cat', 'c.t', null, 1, 0, 'i') AS "3",
    REGEXP_REPLACE('Cat', 'c.t', 'dog', null, 0, 'i') AS "4",
    REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, null, 'i') AS "5",
    REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, null) AS "6"
FROM DUAL;

Result:

      1      2       3       4       5      6 
_______ ______ _______ _______ _______ ______ 
null    Cat    null    null    null    Cat   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Wrong Number of Arguments

Passing no arguments to the function, or too few, results in an error:

SELECT REGEXP_REPLACE()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REGEXP_REPLACE()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

The same with passing too many arguments:

SELECT REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, 'i', 'oops')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, 'i', 'oops')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action:

More Information

The REGEXP_REPLACE() function (as well as Oracle’s other implementation of regular expressions) conforms with the IEEE Portable Operating System Interface (POSIX) regular expression standard and to the Unicode Regular Expression Guidelines of the Unicode Consortium.

See the Oracle documentation for more information and examples of the REGEXP_REPLACE() function.