Re: Oracle SQL and Like Clause

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 Fri Jan 19 2001 - 20:26:46 CST