SQL Snippets ™: Tutorials for Oracle Developers

If separate variables are not an option and dynamic SQL is a "no no" then the following approach is the next simplest technique. It will not perform well in most cases however because its design prevents the use of any indexes that may exist on KEY.

variable d varchar2

execute :d := ','

variable p varchar2(100)

execute :p := '1,3,5'

select *
from   t
where  :d || :p || :d like '%' || :d || to_char(key) || :d  || '%' ;
 

       KEY C
---------- ----------
         1 v1
         3 v3
         5 v5
 

For applications where the base table contains a small number of rows or an indexed scan would not be possible regardless of the technique employed, this method may suffice.