Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Upper Case Indexes

Upper Case Indexes

From: Stuart Houlston <Stuart.Houlston_at_bmw.co.uk>
Date: Mon, 25 Sep 2000 16:45:54 +0100
Message-Id: <10630.117754@fatcity.com>


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. Received on Mon Sep 25 2000 - 10:45:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US