Re: Oracle can be Case Insensitive ?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f570f90$0$563$afc38c87_at_news.optusnet.com.au>...

> So in 8i, they invented function-based indexes. Create index blah on emp
> upper(ename). So now you have an index which *does* contain an entry for
> "BOB", and the earlier query is thus able to make use of index access whilst
> doing its case insensitive search.
>

As an interesting aside:

Often there is a need to ensure that although case is kept in a VARCHAR2 column, there are no duplicates on the column using case insensitive criteria. This is to avoid for example someone entering "cairo" and "Cairo" in a CITY column and both ending up in the table although they are for all intents and purposes a duplicate.

So, how does one ensure the uniqueness across case but allow full case insensitive data entry?

CREATE UNIQUE INDEX TNAME_UKN on TNAME(upper(CITY));

and Bob(but not bob)'s your uncle!

Try that with another database...
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Fri Sep 05 2003 - 00:51:34 CDT