Re: Need help for IN operator in Oracle

The IN clause tells you if a value is in a list of elements. 'var1','var2','var3' is not a list of elements, but a string. So what you actually want to know is if a value is a substring of that string:

SELECT * FROM TEMP
WHERE instr(:A,''''||Col||'''') > 0

sangu_rao_at_yahoo.co.in wrote:

> Hi,
> I need some help in using the IN operator with Oracle 9i. The issue is
> I will get a data in variable (Lets say the variable is "A") with this
> below foramt
>
> 'var1','var2','var3'
>
>
> The word is a combination of 3 words where each word enclosed in a
> single quotes and seperated by a comma operator. This is how i will get
> the string to my SP. Now i have to seach a table to see whether the
> column content of a table matches with the any of the words ( i.e.
> var1, var2 or var3) in the above string. Then my query looks like
>
> SELECT * FROM TEMP WEHRE Col IN A;
>
> The above query will treat the string 'var1','var2','var3' as a one
> single string not as a combination of words. So my requirement won't be
> solved with the above query.

[snip] Received on Fri Dec 15 2006 - 02:35:40 CST