Re: Case Insensitive Where statements: Net8?

If your data is all saved in uppercase, you've got it made.

If you have a customers table, with the last_name stored in upper, just do this:

SELECT LAST_NAME
FROM CUSTOMERS
WHERE LAST_NAME = UPPER(:mySearchlastName)

This will allow Oracle to INDEED use the index.

If you do this:

SELECT LAST_NAME
FROM CUSTOMERS
WHERE UPPER(LAST_NAME) = UPPER(:mySearchlastName)

This would NOT use the index (unless you have an index-based function).

If you are in a situation where the data is stored in mixed case and that the user wants to search in case-insensitive mode, and that you DON'T have index-based functions, you can do this:

Create an extra column that is stored in uppercase, via a trigger.

Whenever you search, do an UPPER() on the user search (not the column name).

Email me if you need more info.

JP
carolek_at_ix.netcom.com (Carole Kaufman) wrote in message news:<72330e0d.0204100613.1359272b_at_posting.google.com>...
> Thanks, Daniel, but function based indexes help if my data is mixed
> case. In this case, my data is in all CAPs, but my users...they want
> to put in anything. Sure, they will train themselves in time to always
> use CAPs, but it would be nice if there was a way to have Oracle via
> query re-write or Sqlnet convert their where's to CAPs. The users are
> just using an ad hoc tool. DB2 now has that, so I was wondering if
> Oracle had something similar now too. Perhaps in the next release....
>
> ck
>
>
> Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CB3407C.E705A77F_at_exesolutions.com>...
> > Function based indexes in which the values are stored in the index as
> > converting by a function. You can use built-in functions provided by
> > Oracle or functions that you create yourself.
> >
> > Daniel Morgan
> >
> >
> >
> > Carole Kaufman wrote:
> >
> > > In DB2 (sorry....) I can configure the DB2 connect middleware to take
> > > user parameters in the WHERE and convert them to upper case so the
> > > query can run efficiently against indexes (all data is in UPPER) and
> > > accurately as far as the user is concerned. Is there anything in Net8
> > > / SQLnet configuration that can do the same thing? Is there anything
> > > that can be turned on via Oracle 9i query re-write that can
> > > automatically turn predicates into caps?
> > >
> > > Thanks for your help,
> > >
> > > Carole
> > > carolek_at_ix.netcom.com
Received on Wed Apr 10 2002 - 15:10:59 CDT