Recently we had a need to replace a string with another string in PL/SQL but we only wanted to do that if it was a whole word string that matched. Also the string could contain an underscore and be part of a code example as we were using dynamic SQL. So we needed to preserve operators in the code.
So for example we might want to replace the string v_cat with v_dog in this code sample.
select emp_id, max(v_cat)
from employees
where v_cat>100
and v_cat_owner is not null;
We don't want to change v_cat_owner though as that is a different string. So we can't just do a normal replace as it will make it v_dog_owner.
After a lot of Google use and some trial and error, the following use of REGEXP_REPLACE was found to work
SELECT REGEXP_REPLACE ('select emp_id, max(v_cat)
from employees
where v_cat>100
and v_cat_owner is not null;', -- input string
'(\W|^)?v_cat(\W|$)' , -- \W is for a nonword character
'\1v_dog\2', -- replace with v_dog
1, -- position, first character
0, -- all occurrences
'i') -- i is case insensitive
FROM dual
Which changes the string to
select emp_id, max(v_dog)
from employees
where v_dog>100
and v_cat_owner is not null;
Further REGEXP_REPLACE info from Oracle at https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm#ADFNS235
