Re: How to do Case Insensitive Search using LIKE operator

From: Fuzzy <grant_at_towersoft.com.au>
Date: 1996/10/29
Message-ID: <32768d1c.2961850_at_newshost.interact.net.au>#1/1


bwit_at_pobox.net (Bob Withers) wrote:

>In article <54qv32$10a_at_cortex.nlm.nih.gov>, wenk_at_tendon.nlm.nih.gov
>says...
>>
>> : Shekhar Kirani <skirani_at_sprynet.com> wrote:
 

>> : >Hi:
 

>> : >In ORACLE is it possible to make a case insensitive search
>> : >on columns where columns contain case sensitive data?
 

>> : >For example, suppose NAMES column contain 'JDoe', 'SKirani' and so on.
>> : >How do I make a SQL like
>> : >SELECT NAMES FROM PERSON WHERE NAMES LIKE %sKi%
>> : >return the 'SKirani' row?
>>
>> You can do case-insensitive searching with the Context Option of 7.3.2
>> which offers the "contains" clause. There's a fair amount of overhead
>> involved but it does allow you to perform a case-insensitive search
>> directly against the indices.
>
>How about:
>
>select names from person where upper(names) like '%SKI%';
>
>Regards,
>Bob

any use of a function like this immediately cripples Oracle, as it then can't use any indexes.

On small db's this is fine, on real-world ones it sucks!

Oracle should pull their finger out and either

  • allow the server to be set-up in case-insensitive mode
  • allow indexes on functions of attributes.

(Can you tell that I'm P****** O** about this?)

Ciao
Fuzzy
:-) Received on Tue Oct 29 1996 - 00:00:00 CET

Original text of this message