APEX-AT-WORK no image

Oracle Select: Compare two comma separated lists for matching words

Von Tobias Arnhold 11.17.2011
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 
*/

Post Tags:

No Comment to " Oracle Select: Compare two comma separated lists for matching words "