using oracle regexp_replace to replace some part of a code

Question

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.

Answer 1

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>

Answer 2

Maybe we could phrase this as: replace 'BB' that is not followed by '/'?

regexp_replace(myval, 'BB($|[^/])', 'XX\1')

Demo on DB Fiddle:

with t as (
    select 'AA/BB' myval from dual 
    union all select 'AA/BB1' from dual
    union all select 'AA/BB-1' from dual
    union all select 'BB' from dual
    union all select 'BB' from dual
    union all select 'BB1' from dual
    union all select 'BB-1' from dual
    union all select 'BB/BB' from dual
)
select myval, regexp_replace(myval, 'BB($|[^/])', 'XX\1') newval from t

MYVAL   | NEWVAL 
:------ | :------
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