The REGEXP_REPLACE function in Oracle PL/SQL is a powerful tool for performing regular expression-based search and replacement within strings. This function allows you to search a string for a specified pattern using regular expressions and replace it with a specified value. Here is an overview of the REGEXP_REPLACE function:


REGEXP_REPLACE(source_string, pattern, replace_string [, start_position [, match_occurrence [, match_return_option [, match_parameter ]]]])

source_string: The input string where the search and replacement will be performed.

pattern: The regular expression pattern to search for in the source_string.

replace_string: The string that will replace the matched pattern in the source_string.

start_position (optional): The position in the source_string to start the search. If not specified, the search starts from the beginning.

match_occurrence (optional): The occurrence of the match to replace. If not specified, all occurrences are replaced.

match_return_option (optional): Specifies what to return after the replacement. Possible values are ‘MATCHES’, ‘NO MATCHES’, ‘ALL’, or ‘FIRST’. If not specified, ‘ALL’ is the default.

match_parameter (optional): Additional matching options, such as case-insensitivity or multi-line matching.


REGEXP_REPLACE('The quick brown fox jumps over the lazy dog', 'fox', 'cat') AS replaced_text
FROM dual;

In this example, the function searches for the pattern ‘fox’ in the source string and replaces it with ‘cat’. The result would be:

The quick brown cat jumps over the lazy dog


Simple Replacement

REGEXP_REPLACE('apple, banana, cherry', 'banana', 'grape') AS replaced_text
FROM dual;

Output: apple, grape, cherry

Case-Insensitive Replacement

REGEXP_REPLACE('Apples and oranges are fruits', 'apples', 'pears', 1, 1, 'i') AS replaced_text
FROM dual;

Output: pears and oranges are fruits

Replace Multiple Occurrences

REGEXP_REPLACE('red green red blue red yellow', 'red', 'purple', 1, 0) AS replaced_text
FROM dual;

Output: purple green purple blue purple yellow

Return Only the First Match

REGEXP_REPLACE('one two three four five', '\w+', 'X', 1, 1, NULL, NULL) AS replaced_text
FROM dual;

Output: X two three four five

The REGEXP_REPLACE function in Oracle PL/SQL provides a flexible way to manipulate string data using regular expressions, allowing for advanced search and replace operations within your database queries and procedures.