Oracle 18c

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

CREATE TABLE table_name ( column1, column2 ) AS
SELECT 'data1', 'AAA,CCC,BBB' FROM DUAL UNION ALL
SELECT 'data2', 'AAA,CCC'     FROM DUAL UNION ALL
SELECT 'data3', 'BBB,DDD,EEE' FROM DUAL UNION ALL
SELECT 'data4', 'EEE,FFF,GGG' FROM DUAL;
4 rows affected

SELECT *
FROM   table_name
WHERE  ','||column2||',' LIKE '%,AAA,%'
OR     ','||column2||',' LIKE '%,BBB,%'


COLUMN1
COLUMN2


data1
AAA,CCC,BBB

data2
AAA,CCC

data3
BBB,DDD,EEE


SELECT *
FROM   table_name t
WHERE  EXISTS (
         SELECT 1
         FROM   TABLE(SYS.ODCIVARCHAR2LIST('AAA','BBB'))
         WHERE  ','||t.column2||',' LIKE '%,'||COLUMN_VALUE||',%'
       );


COLUMN1
COLUMN2


data1
AAA,CCC,BBB

data2
AAA,CCC

data3
BBB,DDD,EEE