sql - Comparing two comma delimited strings -

i have 2 strings str1: 'abc,def,ghi' , str2: 'tyu,abc,fgh'.

i want compare these 2 strings using delimiter ,. since 2 strings have abc should return true. want function in oracle sql can perform operation.

looks complicated couple of helper functions split list separate values (to contained table type) , simple function test intersection of 2 collections.

oracle setup:

create type varchar2s_table table of varchar2(4000);  create function regexp_escape(   expression varchar2 ) return varchar2 deterministic begin   return regexp_replace( expression, '([$^[()+*?{\|])', '\\\1', 1, 0, 'c' ); end; /  create function splitlist(   list  varchar2,   delim varchar2 := ',' ) return varchar2s_table deterministic   pattern   varchar2(256);   len       binary_integer;   t_items   varchar2s_table := varchar2s_table(); begin   if list null     null;   elsif delim null     t_items.extend( length( list ) );     in 1 .. length( list ) loop       t_items(i) := substr( list, i, 1 );     end loop;   else     pattern   := '(.*?)($|' || regexp_escape( delim ) || ')';     len       := regexp_count( list, pattern ) - 1;     t_items.extend( len );     if len = 1       t_items(1) := list;     else       in 1 .. len loop         t_items(i) := regexp_substr( list, pattern, 1, i, null, 1 );       end loop;     end if;   end if;   return t_items; end; /  create function check_list_intersect(   list1 varchar2,   list2 varchar2 ) return number deterministic begin   if splitlist( list1 ) multiset intersect splitlist( list2 ) empty     return 0;   else     return 1;   end if; end; / 

query 1:

select check_list_intersect( 'abc,def,ghi', 'abc' ) matches   dual; 

results:

 matches ---------        1 

query 2:

select check_list_intersect( 'abc,def,ghi', 'abcd' ) matches   dual; 

results:

 matches ---------        0