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

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

Re: Upper Case Indexes

From: Oliver Artelt <oli_at_md.transnet.de>
Date: Mon, 25 Sep 2000 18:29:49 +0200
Message-Id: <10630.117761@fatcity.com>


How about:

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:

CREATE INDEX uppercase_idx ON emp (UPPER(empname));

can facilitate processing queries such as this:

SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';

check it out,
oli

On Mon, 25 Sep 2000, Stuart Houlston wrote:
> 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

-- 
---

Oliver Artelt, System- und Datenbankadministration
---------------------------------------------------------------
  cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
   email: oli@cubeoffice.de # web: http://www.cubeoffice.de
Received on Mon Sep 25 2000 - 11:29:49 CDT

Original text of this message

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