Re: Case Sensitivity in Oracle for NT

Vijay Darekar <vijayd_at_worldnet.att.net> wrote in article <33A77C82.45BF_at_worldnet.att.net>...
> Elliot Ross wrote:
> >
> > Can anyone explainn how to have Oracle sort case insensitive ?? I am
> > trying to have tables (such as Customers) that sort case insensitive.
> >
> > If I do a select ... from .... Where C_NAME = SMITH etc.
> >
> > I want to get SMITH, Smith, smith, sMitTh etc and so on !!
> >
> > thanks !
> >
> > --
> > My EMail address contains a # to try to
> > thwart mail spamming.
>
> Use UPPER function in the where clause that will give you all kinds of
> smith.
>
> select .....
> from customer
> where upper(c_name) = 'SMITH'
>
>

There is no config option to make Orcale case insensite for sorting etc. And the above solution will disable any indexes that you have, making the query slow.

Either keep extra columns (maintained by triggers) with upper case versions of things (or even better, soundexes?). Or, for queries:

where (customer like 'SM%' or customer like 'sm%' or customer like 'Sm%' or customer like 'sm%) and upper(customer) = 'SMITH'. This will not disable the index, and is how Forms (from v3 onwards, ISTR) does such queries.

I don't understand why the oracle query optimizer could not be special cased to handle this (with hint to allow you to disable this optimization).  But they haven't dont it yet.

-- 
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.