Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using LIKE with IN on an expression
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
Received on Sat Jan 27 2007 - 11:55:07 CST