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:
Syntax
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.
Example
SELECT
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
Usage
Simple Replacement
SELECT
REGEXP_REPLACE('apple, banana, cherry', 'banana', 'grape') AS replaced_text
FROM dual;
Output: apple, grape, cherry
Case-Insensitive Replacement
SELECT
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
SELECT
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
SELECT
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.