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 :)

CodePudding user response：

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:

REPLACED_VALUE
null
bbb
ccc
ddd,eee
fff,ggg,hhh
aaa

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:

REPLACED_VALUE
null
bbb
ccc
ddd,eee
fff,ggg,hhh
null

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

CodePudding user response：

You may use a regex replacement approach here:

SELECT val, TRIM(BOTH ',' FROM REGEXP_REPLACE(val, 'aaa,|,aaa,|,aaa', ',')) AS val_out
FROM yourTable;

Demo

If you require an update, then use:

UPDATE yourTable
SET val = TRIM(BOTH ',' FROM REGEXP_REPLACE(val, 'aaa,|,aaa,|,aaa', ','))
WHERE ',' || val || ',' LIKE '%,aaa,%';