I have some codes and I want to replace a peace of that code only. in my case BB to XX
AA/BB
AA/BB1
AA/BB-1
BB
BB1
BB-1
I tried use to regexp_replace with this simple form
Query:
select regexp_replace('AA/BB','BB','XX') from dual;
Result:
AA/XX
Query:
select regexp_replace('AA/BB-1','BB','XX') from dual;
Result:
AA/XX-1
It works fine but it can happen that before the slash AA will be BB as well but this time it shouldn't be replaced but still works for the rest of codes.
select regexp_replace('BB/BB','BB','XX') from dual;
gives me XX/XX of course but I want to achieve BB/XX etc.
Alternatively - see comments within code, where "starting at position" reads as: if there are more than 1 BB substrings there, start at position of the 2nd BB within the MYVAL. Otherwise, start from the beginning of MYVAL.
Thank you, @GMB, for sample data.
SQL> with t as (
2 select 'AA/BB' myval from dual
3 union all select 'AA/BB1' from dual
4 union all select 'AA/BB-1' from dual
5 union all select 'BB' from dual
6 union all select 'BB' from dual
7 union all select 'BB1' from dual
8 union all select 'BB-1' from dual
9 union all select 'BB/BB' from dual
10 union all select 'AA/BB/BB-2' from dual
11 )
12 select myval,
13 regexp_replace
14 (myval, --> in MYVAL
15 'BB', --> replace BB
16 'XX', --> with XX
17 case when regexp_count(myval, 'BB') = 1 then 1 --> starting at position*
18 else instr(myval, 'BB', 1, 2)
19 end
20 ) result
21 from t;
MYVAL RESULT
---------- ---------------
AA/BB AA/XX
AA/BB1 AA/XX1
AA/BB-1 AA/XX-1
BB XX
BB XX
BB1 XX1
BB-1 XX-1
BB/BB BB/XX
AA/BB/BB-2 AA/BB/XX-2
9 rows selected.
SQL>