Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Upper Case Indexes
In 8.x you are supposed to be able to have function based indexes. Simply =
make an upper case index on your name column.
>>> "Smith, Ron L." <rlsmith_at_kmg.com> 09/25/00 01:21PM >>>
Could you create another column that is derived from the name field except
all values are stored in uppercase?
-----Original Message-----
Sent: Monday, September 25, 2000 11:51 AM
To: Multiple recipients of list ORACLE-L
All,=20
I have a problem with searching a name column within the database where =
the
case of the data must be preserved. This name is then used in the search
criteria of many queries in the application.
The data is
Table DEALERS
Column NAME
Data
BMW Freecars Ltd
BMW FREECARS ltd
BMW FreeCars LTD
Table DEALERS will contain several thousand entries and an index is placed on the NAME column to assist with lookups. These are all valid entries but the developers want to be able to search = on=20
SELECT *=20
FROM DEALERS
WHERE NAME =3D 'BMW FREECARS LTD'
This should bring back the 3 rows above.
I could of course use the UPPER function around the column name but this will invalidate the use of my carefully designed index strategy and cause the query to take a thousand years to complete (well a few seconds = anyway).
The only suggestion that I have come up with is to have a second column
called UPPER_NAME on the table that hold all the values from NAME in
UPPERCASE. This could then be indexed and used in the search criteria. =
This
column will be maintained by the use of database triggers. A bit messy but
it should work.
Any other helpful hints and tips would be gratefully accepted.
Many Thanks
Stuart
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
--=20
Author: Stuart Houlston
INET: Stuart.Houlston_at_bmw.co.uk=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Mon Sep 25 2000 - 11:55:37 CDT
![]() |
![]() |