Re: SQL: Combine LIKE and IN statement?

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0402111800.3d4a3768_at_posting.google.com>...
> Bob <no_at_mail.com> wrote in message news:<sBuWb.531553$_x2.1127068_at_zonnet-reader-1>...
> > Can anyone tell me how to improve the following:
> >
> > Select tbl.city
> > From tbl
> > Where tbl.city like 'B%' or
> > tbl.city like 'G%' or
> > tbl.city like 'R%'
> >
> > I was trying:
> > Where tbl.city like in ('B%', 'G%', 'R%')
> > But apparently this is not the right way.
> >
> >
> > Thanx,
> > Bob
>
> Bob, is there an index on the city column? How about posting the
> explain plan for the statement so we can see how Oracle choose to
> solve the query? Are your running cost or rule based? Are the
> statistics current? etc....
>
> HTH -- Mark D Powell --

I can see where this OR/LIKE thing would be an issue when the like criteria is more "like" '%X%'

but in your example couldn't you just have done this...

Select tbl.city
From tbl
Where substr(tbl.city, 1, 1) in ( 'B', 'G', 'R' )

not sure if index will be used in this case...

Dave Received on Thu Feb 12 2004 - 03:01:29 CST