Re: Searching case-insensitively

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 Received on Thu Jul 11 1996 - 00:00:00 CEST