Execute update replace when value does not contain specified value

Question

I'm trying to create query that will replace in all rows (whole column) value to another value if it does not already exist.

Example:

Let's say I have column with values:

1. ABC|DEF|GHI|BLA
2. ABC|GHI
3. ABC
4. GHI
4. GHI|JKL

And let's say I want to replace ABC, DEF,GHI with one JKL value value IF that JKL value not exists already. So the result should be:

1. JKL|BLA (because I didn't want to replace `BLA`)
2. JKL (because `ABC` AND `GHI` should be replaced with `JKL`)
3. JKL 
4. JKL
4. JKL (because `GHI` should be replaced with `JKL`, but we have already `JKL` value so we just remove `GHI`

I have made query that will work, but it is so long:

UPDATE TABLE1
            SET COLUMN1 = regexp_replace(COLUMN1, 'ABC|DEF|GHI|(ABC\|DEF\|GHI)|(ABC\|GHI)|(GHI\|JKL)', 'PLS')

it will be much longer if for example I will have combination ABC|JKL and I will have to add another condition to this query. Could you help me to tune it?

Answer 1

Here is a possible solution.

We start with adding JKL|.
|| is the concatenation on Oracle.
'(ABC|DEF|GHI|JKL)\|?' means ABC or DEF or GHI or JKL followed by an optional pipe. We remove them (replace with an empty string '' ). It's simpler to remove JKL and add it again than to leave it a deal with duplicates. We have to escape the pipe \| because it means OR in regex. ? means 0 or 1 occurance. We could develop it further to avoid adding the pipe when they is no other group.
Finally in an outer, second, regexp_replace we remove a pipe if it is the last character before the end of the string $.

create table cols(
column1 VARCHAR (100));

?

insert into cols (column1)
select 'ABC|DEF|GHI|BLA' from dual
union all
select 'ABC|GHI' from dual
 union all
select 'ABC' from dual
union all
select 'GHI' from dual
union all
select 'GHI|JKL' from dual
union all 
select 'XYZ' from dual;

6 rows affected

update cols
set column1 =
  regexp_replace('JKL|' ||
  regexp_replace(
     COLUMN1
     , '(ABC|DEF|GHI|JKL)\|?','')
     ,'\|$','')
where 
regexp_like(column1, 'ABC|DEF|GHI')

5 rows affected

select * from cols

| COLUMN1 |
| :------ |
| JKL|BLA |
| JKL     |
| JKL     |
| JKL     |
| JKL     |
| XYZ     |

db<>fiddle here