Re: Using LIKE with IN on an expression

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 27 Jan 2007 13:12:57 -0800
Message-ID: <1169932374.800649@bubbleator.drizzle.com>

Grant wrote:

> Hi
> 
> I should have qualified my earlier question... I am attempting to pass 
> a variable containing  multiple selections from a list box ( and in 
> one instance , a string of comma delimited entries from a user text 
> box.)  I am kinda new to Oracle and wasn't sure whether I needed to 
> use LIKE in conjunction with IN on the statement.  As I understand, 
> LIKE is a good way to broaden the range of possible returns, whereas 
> IN is the way to enumerate and compare a list of values for a return.  
> I was thinking that I could apply the benefit of LIKE on each member 
> in the list collection.    Im not really sure if that matters with an 
> IN clause or not.  If I say "IN ('%MyVarList%')  I am not sure if it 
> is constrained to an exact match or not)  If I use LIKE with it, 
> wouldn't that be less constrained on the matching criteria?
> 
> Sorry if this sounds screwy...I am still novice to the DB world
> 
> 
> Thanks
> 
> On Jan 26, 5:09 pm, "Steve Howard" <stevedhow..._at_gmail.com> wrote:

>> On Jan 26, 4:04 pm, "Grant" <grant.Collinswo..._at_eds.com> wrote:
>>
>>> Hi,
>>> The following Statement:
>>> WHERE x.field LIKE (IN(%MyCommadelimetedValue%))
>>> Is that thecorrect syntax to perfom a comparison on values brought in
>>> from a selection combo box? (Iam assuming that Oracle is able to
>>> perform such an operation)
>>> Thanks
>>> GrantHi,
>> You are looking for something like what is below?
>>
>> SQL> create table t0126(c varchar2(10));
>>
>> Table created.
>>
>> SQL> insert into t0126 values('foo');
>>
>> 1 row created.
>>
>> SQL> insert into t0126 values('bar');
>>
>> 1 row created.
>>
>> SQL> insert into t0126 values('redsox');
>>
>> 1 row created.
>>
>> SQL> insert into t0126 values('redfoo');
>>
>> 1 row created.
>>
>> SQL> commit;
>>
>> Commit complete.
>>
>> You want something like ...
>>
>> SQL> select from t0126 where c like(in('%red%','%foo%'));
>> select from t0126 where c like(in('%red%'))
>> *
>> ERROR at line 1:
>> ORA-00936: missing expression
>>
>> SQL>
>>
>> One way would be to pass the selected values in the combo box to a
>> procedure which builds a dynamic OR'd WHERE clause. Read up on dynamic
>> SQL at tahiti.oracle.com, paying close attention to using bind
>> variables whenever you build a string like this.
>>
>> http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/d...
>>
>> HTH,
>>
>> Steve

What you are looking for can be found at: www.psoug.org
click on Morgan's Library
click on Conditions
scroll down to "Complex IN Demo"

You will need to prepare the value of your variable to look like the one in the demo.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jan 27 2007 - 15:12:57 CST