Re: Case insensitive query.

From: Philip Chee <philip_at_aleytys.pc.my>
Date: 1995/10/11
Message-ID: <813416585.1190snx_at_aleytys.pc.my>#1/1

In article <mcallister.157.3079A3EF_at_grad.missouri.edu> mcallister_at_grad.missouri.edu writes:
>In article <453vfn$ram_at_aadt.sdt.com> "Bobby S. Mukundan" <bobby_at_sdt.com>> writes:
>>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.
 

>There is currently no other way around this.

Correct me if I am wrong, but shouldn't the following select enable Oracle to use an index on COL1?

select * from TABLE
where upper(COL1) = upper(value)

and    ( COL1 like substr(upper(value),1,1)||substr(lower(value),2,1)||'%'
or       COL1 like substr(lower(value),1,1)||substr(upper(value),2,1)||'%'
or       COL1 like substr(upper(value),1,2)||'%'
or       COL1 like substr(lower(value),1,2)||'%' )

Philip

+---------------------------------+-------------------------------------------+
|Philip Chee: philip_at_aleytys.pc.my| This is a work of fiction.Any resemblance |
|Tasek Cement Berhad              |  to persons living or dead is completely  |
|P.O.Box 254, 30908 Ipoh, MALAYSIA| coincidental. The creator of this article |
|Voice: +605-551-011              |    has asserted its moral right to be     |
|  Fax: +605-566-142              |          identified as the author.        |
+-------------------Eigi-Eru-Enn-Allir-Jomsvikingar-Daudir!-------------------+

... When choosing between two evils, select the newer one.

---
 * TLX v4.00 *

Received on Wed Oct 11 1995 - 00:00:00 CET