Oracle regexp_replace in full words

Question

I want to replace the instances of the word OF with "OF". I just want this to work on complete words. So not on L_OF, DOF, OFZ, DOFD, OF_L, etc.

My code works below, except for the final line.

It returns:

("OF"*OF + 2) 

... instead of:

("OF"*"OF" + 2) 

How can I get him to work on this?

with stg as
(
select '(ofof+ol)' str from dual union all
select '(oof+ol+of)'   from dual union all
select '(*of + 2)'     from dual union all
select '(of*of + 2)'   from dual 
)
select str,
       regexp_replace(upper(str), '(\W|^)(OF)(\W|$)', '\1"OF"\3') as str2
from   stg

Answer 1

Here's one way to do this - with a recursive query (requires Oracle 11.2 or higher). Do not expect it to be fast.

with stg as
(
  select '(ofof+ol)' str from dual union all
  select '(oof+ol+of)'   from dual union all
  select '(*of + 2)'     from dual union all
  select '(of*of + 2)'   from dual 
),
rec ( str, lvl, new_str ) as
(
  select str, 1, upper(str)
    from stg
  union all
  select str, lvl + 1, 
         regexp_replace(new_str, '(\W|^)(OF)(\W|$)', '\1"OF"\3', 1, lvl)
  from   rec
  where  regexp_instr(new_str, '(\W|^)(OF)(\W|$)', 1, lvl) > 0
)
select str, new_str
from   rec
where  regexp_instr(new_str, '(\W|^)(OF)(\W|$)', 1, lvl) = 0
;

STR          NEW_STR          
------------ ------------------
(ofof+ol)    (OFOF+OL)         
(oof+ol+of)  (OOF+OL+"OF")     
(*of + 2)    (*"OF" + 2)       
(of*of + 2)  ("OF"*"OF" + 2)   

Answer 2

This is too long for comment. I do not know the solution, but I understand the problem. You will see it much easier with 'of of', but not with 'of**of'.

, , , . , , , "^" " ". , .