7-15. Finding and Replacing Text Within a String
Problem
You want to replace each occurrence of a given string within a body of text.
Solution
Use the REGEXP_REPLACE function to match a pattern of text against a given body of text, and replace all matching occurrences with a new string. In the following function, the REGEXP_REPLACE function is used to replace all occurrences of the JOB_TITLE ‘Programmer’ with the new title of ‘Developer’.
DECLARE
CURSOR job_cur IS
SELECT *
FROM jobs;
job_rec job_cur%ROWTYPE;
new_job_title jobs.job_title%TYPE;
BEGIN
FOR job_rec IN job_cur LOOP
IF REGEXP_INSTR(job_rec.job_title,'Programmer') > 0 THEN
new_job_title := REGEXP_REPLACE(job_rec.job_title, 'Programmer',
'Developer'),
UPDATE jobs
SET job_title = new_job_title
WHERE job_id = job_rec.job_id;
DBMS_OUTPUT.PUT_LINE(job_rec.job_title || ' replaced with ' ||
new_job_title);
END IF;
END LOOP;
END;
Although this particular example does not use any regular expression patterns, it could be adjusted to do so. To find more information and tables specifying the options that are available for creating patterns, please refer to the online Oracle documentation.
The solution to this recipe prints out the revised text. Each occurrence of the ‘Programmer’ text is replaced with ‘Developer’, and the newly generated string is returned into the NEW_REVIEW variable.
How It Works
The REGEXP_REPLACE function is a great way to find and replace strings within a body of text. The function can be used within any Oracle SQL statement or PL/SQL code. The syntax for the function is as follows:
REGEXP_REPLACE(source_text, pattern, replacement_string, position, occurrence, options)
The source text for the function can be any string literal, variable, or column that has a datatype of VARCHAR2, NVARCHAR2, CHAR, NCHAR, CLOB, or NCLOB. The pattern is a regular expression or a string of text that will be used to match against. The replacement string is will replace each occurrence of the string identified by the source text. The optional position specifies the placement within the source text where the search should begin. By default, the position is 1. The optional occurrence argument is a nonnegative integer that indicates the occurrence of the replace operation. If a 0 is specified, then all matching occurrences will be replaced. If a positive integer is specified, then Oracle will replace the match for that occurrence with the replacement string. The optional options argument includes different useful matching modifiers; please refer to the online Oracle documentation for a listing of the pattern matching modifiers that can be used as options.
Note Do not use REGEXP_REPLACE if the replacement can be performed with a regular UPDATE statement. Since REGEXP_REPLACE uses regular expressions, it can be slower than a regular UPDATE.
The following examples demonstrate how this function can be used within a PL/SQL application or a simple query. This next bit of code demonstrates how to replace numbers that match those within the given set.
Next, we replace a Social Security Number with Xs.
new_ssn := REGEXP_REPLACE('123-45-6789','[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}','xxx-xxx-xxxx'),
Returns: xxx-xxx-xxxx
The REGEXP_REPLACE function can be most useful when attempting to replace patterns of strings within a given body of text such as the two previous examples have shown. As noted previously, if a standard UPDATE statement can be used to replace a value, then that should be the first choice, because regular expressions perform slightly slower.