How Update / change part of string in Oracle Database

I must Update Oracle database with remove part of string, problem is that this part can be in multiple place in this string. Example:

I must remove part and Update database where clolumn_name like (‘%,aaa%’) from

‘bbb,aaa,ccc,ddd’ or ‘bbb,ccc,aaa,ddd’ or from ‘bbb,ccc,eee,fff,aaa,ddd’

Please help me 🙂

>Solution :

To replace complete terms using simple string functions (which is much quicker than regular expressions) is:

SELECT TRIM(
         BOTH ',' FROM
         REPLACE(','||value||',', ',aaa,', ',')
       ) AS replaced_value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'aaa,bbb' FROM DUAL UNION ALL
SELECT 'ccc,aaa' FROM DUAL UNION ALL
SELECT 'ddd,aaa,eee' FROM DUAL UNION ALL
SELECT 'fff,aaa,ggg,aaa,hhh' FROM DUAL UNION ALL
SELECT 'aaa,aaa,aaa' FROM DUAL;

Outputs:

Note: if you can have multiple sequential terms, as per the last example, then using simple string functions will not work; but in other cases when ther are non-sequential repeated terms it will work.

If you can have multiple sequential repeated terms then you can use REGEXP_REPLACE:

SELECT TRIM(
         BOTH ',' FROM
         REGEXP_REPLACE(','||value||',', '(,aaa)+,', ',')
       ) AS replaced_value
FROM   table_name

Which outputs:

fiddle

As for your comment:

I must update database where column_name like ('%,aaa%,)

If you want to remove aaa from the start of each term (and remove empty terms) then:

UPDATE table_name
SET value = TRIM(
              BOTH ',' FROM 
              REGEXP_REPLACE(
                REGEXP_REPLACE(value, '(^|,)aaa', '\1'),
                ',{2,}',
                ','
              )
            )
WHERE value LIKE '%,aaa%'
OR    value LIKE 'aaa%'

fiddle