Re: How to do Case Insensitive Search using LIKE operator
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