Change the delimeters of a text field !!!!!!!!!!!!

[GERPAT]

I am running the following query.

select a.spkeywords,b.eventname
from eventlist a, event b
where b.spid in a.list

"list" is a field containing a list of event ids. I want to return all events that appear in this list. The problem is my list is delimited by a comma but oracle needs the list to be delimeted in the following way: 'id','id'. My question is how to I change the delimiters in the list field so that I can perform an IN operation.

 

[sem]

Oracle needs the list to be the LIST, not the STRING, representin comma delimited list. Even changing "id,id" "'id','id'" will not help you. Though, in some cases you may play with INSTR function. If ID field is integer you may change your condition to

where instr(','||a.list||',', ','||b.spid||',')<>0