用另一个字符串加上各自的位置替换字符串

递归cte方法。

with cte(string,col,cnt,repl) as
(select string,1,regexp_count(string,'st'),regexp_replace(string,'st','pos_'||to_char(1),1,1) as repl
 from test
 union all
 select string,col+1,cnt,regexp_replace(repl,'st','pos_'||to_char(col+1),1,1) as repl
 from cte
 --join it to the original table if there are multiple rows, on string column.
 where col<cnt
)
cycle col set cycle to 1 default 0
select string,repl
from cte
where cnt=col

这里是一个使用递归连接时序估计的稍有不同的解决方案。它寻找 st 仅当它被空格(或字符串的开头或结尾)包围时。

with
  inputs ( str ) as (
    select 'aa bbb st sccc dddd st eee fff g st g h i st j k l m st' from dual
    union all
    select 'st abc st st st where st is not st'                      from dual
    union all
    select 'post st stop postal'                                     from dual
  ),
  r ( lvl, str, new_str ) as (
    select  1, str, str
      from  inputs
    union all
    select  lvl + 1, str, 
            regexp_replace(new_str, '( |^)st( |$)', '\1pos_' || lvl || '\2', 1, 1)
      from  r
      where regexp_like(new_str, '( |^)(st)( |$)')
  )
select str, new_str
from   r
where  not regexp_like(new_str, '( |^)(st)( |$)')
;

STR                                                      NEW_STR
-------------------------------------------------------  ----------------------------------------------------------------------
post st stop postal                                      post pos_1 stop postal
aa bbb st sccc dddd st eee fff g st g h i st j k l m st  aa bbb pos_1 sccc dddd pos_2 eee fff g pos_3 g h i pos_4 j k l m pos_5
st abc st st st where st is not st                       pos_1 abc pos_2 pos_3 pos_4 where pos_5 is not pos_6

使用 MODEL 条款:

select m_1
from dual
model dimension by (0 as key)
measures (cast('st post aa bbb st cccc dddd st ee ffff g st g h i st j k l m st' 
                as varchar2(500)) as m_1 )
rules iterate (100) until(not regexp_like(m_1[0], '( |^)(st)( |$)'))
(m_1[0] = regexp_replace(m_1[0],
          '( |^)st( |$)','\1pos_'||to_char(ITERATION_NUMBER+1)||'\2',1,1));

DBFiddle Demo

输出:

pos_1 post aa bbb pos_2 cccc dddd pos_3 ee ffff g pos_4 g h i pos_5 j k l m pos_6