Re: Case Insensitive Searches In Oracle

From: Miles Thomas <thomasm_at_>
Date: 1997/05/22
Message-ID: <01bc669b$f29bebc0$4621ea9e@UKP01436.logica.co.uk>#1/1

Chuck Erickson <beachst.cerick01_at_eds.com> wrote in article <3383971E.12BD_at_eds.com>...
> Does anyone know of any DB wide option to allow ORACLE to do case
> insensitive compares?

There is no such option, in the server, afaik. (Forms 3 and 4.5 have options to do this in the form, however).

>[snip]

> - SELECT * FROM EMP WHERE
> ( UPPER('smith') = UPPER(EMP_NAME) AND
> (EMP_NAME LIKE 'Sm%' OR EMP_NAME LIKE 'sM%' OR
> EMP_NAME LIKE 'SM%' OR EMP_NAME LIKE 'sm%');
> does force the optimizer to use the index, but is tedious to code, you
> can't expect a end-user to use in an ad-hoc environment, and I'm
> concerned about future changes to the optimizer invaliding the concept.
>

I suspect that Oracle will always support this in the optimizer--Forms3 and 4.5 applications emit SQL just like this when case-insensitive searching is set up for the (block/field).

> - Storing a separate upper-case version of each field used to search.
> EMP_NAME_UPPER would be populated by a trigger that would uppercase the
> value of EMP_NAME. EMP_NAME_UPPER would be indexed and used for
> searches, while EMP_NAME would contain the mixed case value. This option
> uses extra space and you have to educate developers and users how to do
> searches.

You could always create ad-hoc query views where EMP_NAME_UPPER is renamed EMP_NAME, and EMP_NAME renamed to EMP_NAME_MIXED (or some such.) In fact, creating easy to use query views (with some tables pre-joined efficiently) for ad hoc querying is always a good idea--usually gives a runtime improvement (by discouraging badly-formed queries) for a small maintenance overhead.

-- 
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.

Received on Thu May 22 1997 - 00:00:00 CDT