You can create a function based index on lower of the column. However,
doing %program startup% won't use an index, function based or otherwise,
because you are starting with %.
Jim
"Enkidu Utnapishtim" <utnapishtim43NOSPAM_at_hotmail.com> wrote in message
news:3D38A6E1.8030904_at_hotmail.com...
> Brad,
> Well, one option is to change your WHERE to something like:
>
> where lower(MyField) like '%program startup%'
>
> The drawback to this is that as soon as you put that 'lower' function in
> there, you pretty much can kiss any usage of your index good-bye. Of
> course, putting that '%' at the beginning of the literal will do the
> same thing, so maybe you're okay. I assume that since you can't add an
> index, you can't add stored procedures either (since that would give you
> another option)?
>
> HTH,
> Roger Crowley - DBA - LearningFramework
>
> Brad Pybus wrote:
> > I would like to do a query like this in Oracle 8i:
> >
> > select *
> > from MyTable
> > where MyField like '%program startup%'
> >
> > But I want it to search for all the capitalization variations. In
other
> > words, I want it to be case insensitive. How do I do this? And I can't
add
> > an index because its not my database.
> >
> >
> >
>
>
Received on Fri Jul 19 2002 - 22:41:35 CDT
