Re: Upper Case Indexes

Hi,

  If u are running on a version prior to Oracle 8 then the workaround   of creating a additional column and forcing uppercase should be   fine.

  Additionally while populating the new additional column, strip   all spaces, punctuations, special characters etc before storing   the column value. This would ensure that the hit ratio is high when   this column is searched on.

Regards
Rajagopal Venkataramany

On Mon, 25 Sep 2000 08:51:13 -0800, ORACLE-L_at_fatcity.com 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
>
> --
> 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).

Regards
Rajagopal Venkataramany