| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Upper Case Indexes
Could you create another column that is derived from the name field except
all values are stored in uppercase?
-----Original Message-----
From: Stuart Houlston [mailto:Stuart.Houlston_at_bmw.co.uk]
Sent: Monday, September 25, 2000 11:51 AM
To: Multiple recipients of list ORACLE-L
Subject: Upper Case Indexes
All,
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
SELECT *
FROM DEALERS
WHERE NAME = '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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stuart Houlston
INET: Stuart.Houlston_at_bmw.co.uk
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). Received on Mon Sep 25 2000 - 11:17:24 CDT
 |
 |