Re: Searching case-insensitively

Ravi_Kasarla_at_wb.xerox.com wrote:

>
>In article <31E0C1A7.6113_at_mpimg-berlin-dahlem.mpg.de>, <mercer_at_mpimg-berlin-dahlem.mpg.de> writes:
>> Path: rocksanne!parc!news.Stanford.EDU!bloom-beacon.mit.edu!news.mathworks.com!fu-berlin.de!shrew.rz-berlin.mpg.DE!not-for-mail
>> From: Simon Mercer <mercer_at_mpimg-berlin-dahlem.mpg.de>
>> Newsgroups: comp.databases.oracle
>> Subject: Re: Searching case-insensitively
>> Date: Mon, 08 Jul 1996 10:07:03 +0200
>> Organization: Max-Planck-Institut fuer Molekulare Genetik
>> Lines: 25
>> Message-ID: <31E0C1A7.6113_at_mpimg-berlin-dahlem.mpg.de>
>> NNTP-Posting-Host: shrew.rz-berlin.mpg.de (141.14.131.88)
>> Mime-Version: 1.0
>> Content-Type: text/plain; charset=us-ascii
>> Content-Transfer-Encoding: 7bit
>> X-Access: 16 25 816
>> X-Mailer: Mozilla 2.0 (X11; I; OSF1 V3.2 alpha)
>>
>> Simon Mercer <mercer_at_mpimg-berlin-dahlem.mpg.de> wrote:
>>
>> >I have a table with two fields, 'ID' and 'name' with maybe 2 million
>> >records, and I want to conduct a search on the contents of the 'name'
>> >field. This field can contain any alphanumeric string, often with a
>> >mixture of upper and lower case characters. What is the best way to
>> >search this table case-insensitively?
>>
>> Well, so far I know that if I use functions I can't use indices, and
>> that there is no other way to switch to a default case-insensitive
>> searching behaviour. Forcing text entry in uppercase is not an option
>> because case has meaning in these text strings.
>>
>> So there's no way to do this (unless someone knows different)....
>>
>> Surely this is a MASSIVE shortcoming in Oracle, or am I the first person
>> in the world who needs an efficient case-insensitive search?
>>
>> Simon
>>
>> --
>> Dr. Simon Mercer
>> The Reference Library Database
>> +49 30 8413 1395 (fax)
>> mailto:mercer_at_mpimg-berlin-dahlem.mpg.de
>>
>You could use upper/lower to make it case insensitive
>
>select count(*)
>from XXXXX
>where upper(name) = upper('Ravi') ;
>
>This is case insensitive, would give all Ravi, ravi, raVi....
>
>
>Ravi
>Ravi_Kasarla_at_wb.xerox.com
>

True, but it also prevents the use of indexes.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!