Oracle REGEXP_REPLACE() Function

Oracle REGEXP_REPLACE() is a built-in function that replaces a string with new content that matches a specified regular expression.

You can think of this function as an extension of the REPLACE() function with regular expression capabilities.

Oracle REGEXP_REPLACE() Syntax

Here is the syntax for the Oracle REGEXP_REPLACE() function:

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

Parameters

source_char

Required. The string to search. It can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

pattern

Required. The regular expression pattern. It can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can be up to 512 bytes long.

replace_string

Optional. The new content to replace with.

position

Optional. An integer indicating the starting position for the search. The default is 1.

occurrence

Optional. An integer indicating which occurrence to return. The default is 0, which means replace all occurrences.

match_param

Optional. The matching parameter to use. It can be of data types VARCHAR2 or CHAR. You can change the default search behavior with this parameter. You can use one or more of the following characters:

  • 'i' specifies case-insensitive matching, even if the sort order of the determination distinguishes between uppercase and lowercase.
  • 'c' specifies case-sensitive and accent-sensitive matching, even if the sort order of the determination is case-insensitive or accent-insensitive.
  • 'n' allows a period (.) to match a newline character. If this parameter is omitted, the period does not match newline characters.
  • 'm' treats the source string as multiple lines. Oracle interprets the caret (^) and dollar sign ($) as the start and end of any line in the source string, not just the start or end of the entire source string. If this parameter is omitted, Oracle treats the source string as a single line.
  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.

Return Value

The Oracle REGEXP_REPLACE() function replaces a string with new content that matches a specified regular expression and returns the replaced content.

If any parameter is NULL, REGEXP_REPLACE() returns NULL.

Oracle REGEXP_REPLACE() Examples

Here are several examples demonstrating the usage of the Oracle REGEXP_REPLACE() function.

Basic Usage

SELECT
  REGEXP_REPLACE('123 abc 456 def', '\s+', '-') Result
FROM dual;

Output:

RESULT
__________________
123-abc-456-def

Starting Position

The Oracle REGEXP_REPLACE() function allows you to specify the starting position:

SELECT
  REGEXP_REPLACE('123 abc 456 def', '\s+', '-', 5) Result
FROM dual;

Output:

RESULT
__________________
123 abc-456-def

In this example, the search starts at position 5, so the first space is not replaced.

Occurrence

The Oracle REGEXP_REPLACE() function allows you to specify which occurrence to replace:

SELECT
  REGEXP_REPLACE('123 abc 456 def', '\s+', '-', 1, 0) Result1,
  REGEXP_REPLACE('123 abc 456 def', '\s+', '-', 1, 2) Result2
FROM dual;

Output:

RESULT1            RESULT2
__________________ __________________
123-abc-456-def    123 abc-456 def

Note that the default value of occurrence is 0, and Oracle will replace all matching items. Otherwise, Oracle will replace the specified matching item.

NULL Parameters

If any parameter is NULL, REGEXP_REPLACE() will return NULL.

SET NULL 'NULL';
SELECT
    REGEXP_INSTR(NULL, 'a') "Result1",
    REGEXP_INSTR('a', NULL) "Result2",
    REGEXP_INSTR('a', 'b', NULL) "Result3",
    REGEXP_INSTR('a', 'b', '', NULL) "Result4",
    REGEXP_INSTR('a', 'b', '', 1, NULL) "Result5",
    REGEXP_INSTR('a', 'b', '', 1, 1, NULL) "Result6"
FROM dual;

Output:

   Result1    Result2    Result3    Result4    Result5    Result6
__________ __________ __________ __________ __________ __________
      NULL       NULL       NULL       NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle REGEXP_REPLACE() is a built-in function that replaces the content in a string with new content that matches a specified regular expression.