Oracle regexp_replace在完整单词上 [英] Oracle regexp_replace on complete words

问题描述

我想用"OF"替换单词OF的实例.我只希望这能处理完整的单词.因此不在L_OF，DOF，OFZ，DOFD，OF_L等上.

I want to replace instances of the word OF with "OF". I only 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 string.

它返回:

("OF"*OF + 2) 

...而不是:

("OF"*"OF" + 2) 

我怎么也可以在那个上工作呢?

How can I get it to work on that one as well?

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

推荐答案

这是一种实现方法-递归查询(需要Oracle 11.2或更高版本).不要指望它很快.

Here's one way to do this - with a recursive query (requires Oracle 11.2 or higher). Don't 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)   

这篇关于Oracle regexp_replace在完整单词上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！