Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
6 rows affected

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


REPLACED_VALUE


null

bbb

ccc

ddd,eee

fff,ggg,hhh

aaa


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


REPLACED_VALUE


null

bbb

ccc

ddd,eee

fff,ggg,hhh

null