Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!lll-lcc!pyramid!eric
From: eric@pyrps5 (Eric Bergan)
Newsgroups: comp.databases
Subject: Re: Using Indexes in Oracle
Message-ID: <59235@pyramid.pyramid.com>
Date: 15 Feb 89 15:52:49 GMT
Sender: daemon@pyramid.pyramid.com
Reply-To: eric@pyrps5.UUCP (Eric Bergan)
Distribution: na
Organization: Pyramid Technology Corp., Mountain View, CA
Lines: 32

In article <6555@bunny.UUCP> pm04@bunny.UUCP (Preeti Mehta) writes:
>
>Does anyone out there know of a way to perform case-insensitive string matching
>on character strings defined as indexes on an Oracle table?
>
>I am a new user, and have so far read parts of the DBA guide, and the SQL*Plus
>Users guide. As far as I know, you have to know how the indexed string value
>was stored (upper or lower case), in order to get a match in  a query to the 
>database table. Is this correct, or am I missing something? Are there any 
>clever tricks one can use to overcome this problem?

	The usual solution that I know of, assuming you want to make
sure you use the index, is insist that the field you are interested in
is stored in either upper or lower case, and they convert the searched
for string to the appropriate case. Do not do something like

	where uppercase(searchfield) = uppercase(searchstring)

where searchfield is the indexed value. If it is not already stored
uppercase, it will not be able to use the index.

	Of course, if you want to be able to display the database field
with "proper" mixing of upper and lower case, it means you have to have
two fields in the table. One in mixed upper and lower case, only for
display. The other forced to uppercase, and with the index built upon
it. Very common for tables containing names. Also allows you to strip
punctuation, spacing, etc from the searched field, so no concerns
over "van Johnson" and "Vanjohnson".


					eric
					...!pyramid!eric