oracle search word in string

Question

I want to search a word in string in ORACLE in which string is comma separated. Eg. String is ('MF1,MF2,MF3') and now I want to search whether 'MF' exists in that or not. If I am using instr('MF1,MF2,MF3','MF') it will give wrong result since I want to search Full MF in MF1 or MF2 or MF3.

Answer 1

If the incoming string is always in this form:

('X,Y,Z,...')

You could do something like this - replace the quotes with commas, then search for the criterion surrounded with commas:

DECLARE
   in_str VARCHAR2(4000) := '(''MF1,MF2,MF3'')';
   criterion VARCHAR2(100) := 'MF';
BEGIN
   IF INSTR(REPLACE(in_str,'''',','), ',' || criterion || ',') > 0 THEN
      dbms_output.put_line('found!');
   END IF;
END;

So the above would say "found!" for MF1 but not for MF.

Answer 2

Try ..

instr(','||'MF1,MF2,MF3'||',',',MF,')

or more generically

instr(','||col||',' ,  ','||val||',')