alter table emp
add ( tags varchar2(4000))
/

create or replace type tags_tbl_type
as table of varchar2(4000)
/

create or replace 
function csv_to_table
( p_tags_tbl in tags_tbl_type
) return tags_tbl_type
is
  l_tags_tbl tags_tbl_type := tags_tbl_type('');
  l_index integer;
begin
  l_index:= p_tags_tbl.first;   
  loop     
    exit when l_index is null;
    l_tags_tbl := l_tags_tbl multiset union  csv_string( p_string => p_tags_tbl(l_index));
    l_index:= p_tags_tbl.next(l_index);
  end loop;
  return l_tags_tbl;
end csv_to_table;
/





update emp
set    tags = 'sales, deal, offer, discount'
where  job='SALESMAN'
/

update emp
set    tags = 'sales, chief, boss, in charge '
where  ename = 'KING'
/

update emp
set    tags = 'discount, deal, cafetaria, sushi'
where  deptno = 10
/

update emp
set    tags = 'saving, discount, money'
where  sal < 1650
/



select column_value tag
,      count(column_value) number_of_tag_occurrences
from   table (csv_to_table( ( select cast(collect(tags) as tags_tbl_type)
                              from   emp
                             )
                          )
             )
group
by     column_value
order
by     2 desc
/             


  create or replace 
  function csv_string( p_string in varchar2)
  return tags_tbl_type
  is
    l_string varchar2(32000):= p_string;
    l_comma_index integer:=0;
    l_segments_tbl tags_tbl_type  := tags_tbl_type();
    l_ctr number(2):=1;
  begin
    loop
      l_comma_index:= instr( l_string,','); 
      if nvl(l_comma_index,0) > 0
      then
        l_segments_tbl.extend;
        l_segments_tbl(l_segments_tbl.last):= substr(l_string, 1, l_comma_index - 1);
        l_string := trim (both from substr(l_string, l_comma_index + 1));
      else
        l_segments_tbl.extend;
        l_segments_tbl(l_segments_tbl.last):= trim(both from l_string);
        exit;
      end if;
      l_ctr:= l_ctr+1;
    end loop;
    return l_segments_tbl;
  end csv_string;
/



select ename
from   emp
where  exists ( select 0
                from   table( csv_string( emp.tags)) tags
                where  tags.column_value = 'discount'
              )
/

select ename
from   emp
where  0 = ( select count(*) 
             from   table(tags_tbl_type('discount','sales') MULTISET EXCEPT csv_string( emp.tags) ))


has at least two of three tags              


select ename
from   emp
where  2 <= ( select count(*) 
              from   table(  tags_tbl_type('chief','money','discount') 
                             MULTISET INTERSECT 
                             csv_string(emp.tags) 
                          )
            )
              


              
CSV:

SELECT * FROM (
 SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1
                     )
               - INSTR (txt, ',', 1, level) -1 ) )
         AS token
   FROM ( SELECT ','||:in_string||',' AS txt
            FROM dual )
 CONNECT BY level <=
              LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
)