I wonder if it is possible to replace specific string with another string plus position without using PL/SQL block (loops, user defined function/stored procedures, with function construct ...).
Here: st -> pos_num
Input:
"aa bbb st cccc dddd st eeeeeeeeeee ffff g st g h i st j k l m st"
Output:
"aa bbb pos_1 cccc dddd pos_2 eeeeeeeeeee ffff g pos_3 g h i pos_4 j k l m pos_5"
I feel that it is possible to achieve it with one line operation(maybe regex).
A recursive cte approach.
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
Here is a slightly different solution using recursive CTE. It looks for st only when it is surrounded by spaces (or by beginning or end of string).
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 works very well :)Using MODEL clause:
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));
Output:
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
model clause is notoriously slow; but (1) perhaps you don't have a lot of data, so you don't care; and (2) in any case, the only way to know for sure is by testing on your data, not someone else's.
REGEXP_INSTR,REGEXP_REPLACEandLEVELcombination.?'post'with'popos_1'? If not - if 'st' is only replaced if it is a "stand-alone word", what are the rules? (For example: what if it's part of a compound word, like'st-jack'?)st-jackshould not be affected.<space>st<space>should be replaced.