search in text using subtext in Oracle PLSQL

I have this query in master_t table that hold ration_category column and many number of sectors in sectors column like this 'BN:INS' or 'BN' or 'BN:IM:INS' etc..

select distinct ratio_category d from master_t
where status = 'Y' and (SECTORS = :P23_SECTORS or
(INSTR(':'||:SECTORS ||':',:P23_SECTORS)>0 OR UPPER(:P23_SECTORS) = 'ALL')) order by ratio_category


 P23_SECTORS= 'BN:INS'

 SECTORS='INS:MFI:SB:BN'

: is a separator of multi data

I need to check if data inside P23_sectors are exists in SECTORS variable , but this query doesn't get data because the multi data separator

Is there anyway to adjust the query so I could compare subtext with full text note : the order is different.

Expected output : LL PP CC

Thanks

Solution

Here's one option (read comments within code):

SQL> with master_t (ratio_category, sectors) as
  2    -- sample data
  3    (select 1, 'INS:MFI:SB:BN' from dual union all
  4     select 2, 'BN:LF'         from dual
  5    ),
  6  split_t as
  7    -- split SECTORS into rows
  8    (select ratio_category,
  9            sectors,
 10            regexp_substr(sectors, '[^:]+', 1, column_value) sec
 11     from master_t cross join
 12          table(cast(multiset(select level from dual
 13                              connect by level <= regexp_count(sectors, ':') + 1
 14                             ) as sys.odcinumberlist))
 15    ),
 16  split_23 as
 17    -- split P23_SECTORS into rows
 18    (select regexp_substr('&&P23_SECTORS', '[^:]+', 1, level) sec,
 19            regexp_count('&&P23_SECTORS', ':') + 1 cnt
 20     from dual
 21     connect by level <= regexp_count('&&P23_SECTORS', ':') + 1
 22    )
 23  -- return rows that contain complete P23_SECTORS value(s)
 24  select t.ratio_category, t.sectors
 25  from split_t t join split_23 s on s.sec = t.sec
 26  group by t.ratio_category, t.sectors
 27  having count(*) = max(s.cnt);
Enter value for p23_sectors: BN:INS

RATIO_CATEGORY SECTORS
-------------- -------------
             1 INS:MFI:SB:BN

SQL>

Pay attention to comments people posted. They do have serious experience with programming and you'd rather listen to what they say and thank them for spending time to check your problem. Your reply to Ed is rather rude; if I were Ed, I'd make sure not to respond to any of your future questions (read: I'd let you solve your own problems).