Re: Case insensitive query.

From: Andrew McAllister <mcallister_at_grad.missouri.edu>
Date: 1995/10/09
Message-ID: <mcallister.157.3079A3EF_at_grad.missouri.edu>#1/1


In article <453vfn$ram_at_aadt.sdt.com> "Bobby S. Mukundan" <bobby_at_sdt.com> writes:
>From: "Bobby S. Mukundan" <bobby_at_sdt.com>
>Subject: Re: Case insensitive query.
>Date: 6 Oct 1995 19:19:19 GMT
 

>ccrissma_at_ucs.att.com (ccrissma_at_ucs.att.com) wrote:
>>In article <44rsj2$4gt_at_tel.den.mmc.com>, thomas_at_sde.mdso.vf.ge.com (Thomas Kim) writes:
>>>
>>>In Oracle is there a way to perform a case insensitive
>>>query? For example: Select * from table where col1 LIKE 'toys',
>>>where a correct response will include TOYS, Toys, toys, or ToYs etc.

>Try select * from table where upper(col1) = upper(value);

Sorry folks. Try the above query on a huge table. You'll always get a full table scan, and thus long search times.

Using any function like upper(col1) or subst(col1) or lower(etc) will force Oracle to NOT USE INDEXES! This is because Oracle indexes are case sensitive.

To perform case insensitive searches using indexes, you must store the data in one case: upper or lower doesn't matter.

We have a column in our "people" table called ulastname. We use a before update trigger to cause ulastname := UPPER(lastname); Then you can search like:
select * from people where ulastname = UPPER('Smith');

There is currently no other way around this.

Andy
Andrew McAllister
Office of Research
University of Missouri-Columbia
mcallister_at_grad.missouri.edu Received on Mon Oct 09 1995 - 00:00:00 CET

Original text of this message