Re: Case insensitive query.

mcallister_at_grad.missouri.edu (Andrew McAllister) wrote:

>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.
When you make statements like this, you gotta know that your gonna be proven wrong. Yes, yours is perhaps the BEST solution IF you 1. have control over the tables and 2. Have the disk space and 3. are not worried about the small performance hit during insert/update. But it is by NO means the ONLY solution. It does deserve mentioning although it is not specifically an answer to their question, it is a solution for their problem.

Craig

Posted a different solution yesterday so I won't repeat it here. Received on Tue Oct 10 1995 - 00:00:00 CET