Re: Oracle SQL and Like Clause

Good point, although using the LIKE operator with a leading % will still cause a full table scan. Even if the column is indexed, Oracle can't use it because there can be any combination of leading characters.

Doing a LIKE 'XXX%' will use the index, however, so you may still want to create one.

"M" <m_at_n.com> wrote in message news:94at2g$1l1q$1_at_nntp1.ba.best.com...
> Note that such a query (using upper) will not use index on title if title
> column is indexed.
> It will cause a full table scan.
> There may be 2 remedies for that:
> 1. create another indexed column, say upper_title, populated by a trigger:
> :new.upper_title := upper(title);
> and then run searches against upper_title instead of title.
> 2. As of oracle 8i you can use function based index, which is designed for
> exactly this situation.
>
>
> Miro.
>
> "Mark Wagoner" <mwagoner_at_iac.net> wrote in message
> news:94724t$1me0$1_at_genma.iac.net...
> > Convert the text to upper (or lower) case first.
> >
> > select id from articles where upper(title) like '%UNI%'
> >
> > "JF" <john.fitzgerald_at_mainframe.co.uk> wrote in message
> > news:dMD96.1292$eI2.41916_at_NewsReader...
> > > I can't beleive a query like the one below is case sensitive in Oracle
> > >
> > > Select ID from ARTICLES where TITLE like '%Uni%'
> > >
> > > will find records containing...
> > >
> > > big University
> > > Universally etc
> > >
> > > won't find
> > >
> > > UNISON
> > > universal
> > >
> > > Is there any way to use SQL with the Like clause in Oracle so that
 it's
 not
> > > case sensitive?
> > >
> > > I know that I can use interMedia indexing but that's overkill for what
 I'm
> > > doing, i.e. I don't want to index every searchable column in the db.
> > >
> > > The same query worked sweet in SQL Server!
> > >
> > >
> >
> >
>
>
Received on Sat Jan 20 2001 - 10:06:02 CST