Breaking string

[soniaarora]

Hi,

Can someone help me out.
I have a string like this abc#def#hij#klo coming in as a parameter.
Now I need to break this string with delimiter as # and have them in the where condition like
where col in ('abc','def','hij','klo')
Pl. note that this string can have any number of sub-strings like this delimited by #.
How do I do this?

Regards
Sonia

[DaPi]

WHERE INSTR(input_string||'#', col||'#') <> 0
should work, but won't be efficient (can't use an index on col).

[DaPi]

WHERE INSTR('#'||input_string||'#', '#'||col||'#') <> 0
would be even nicer - it should deal with variable length substrings.