This a simple solution to compare two comma separated lists inside a select where clause:
/*
Table name: TBL_WITH_COMMA_LIST
Columns:
twc_id; twc_list
1 ; 0815,9999,1212,1222,1111,9988
2 ; 0815,8888,2121,2111,2222,8899
3 ; 0110,9112,1211
Parameter: :v_list
0815,9988,1111
*/
select tw.twc_id, tw.twc_list, REGEXP_COUNT(tw.twc_list,REPLACE(:v_list,',','|')) ret_val
from TBL_WITH_COMMA_LIST tw
where REGEXP_COUNT(tw.twc_list,
REPLACE(:v_list,',','|')
) > 0;
/*
Result:
twc_id; twc_list; ; ret_val
1 ; 0815,9999,1212,1222,1111,9988; 3
2 ; 0815,8888,2121,2111,2222,8899; 1
Info: v_list can only contain 512 bytes
*/
