Re: Help: Case insensitive sort.

>Prashanth,
>
>As has already been mentioned, you need to sort by the uppercase
>value. However, the upper() function will disable the use of indexes.
>If you are trying to sort by an indexed field and want to retain the
>use of the index, you have a problem if your tables are large.
>

I did not read the original message from Prashanth, so forgive me if I am missing something. My point:

If you are talking about Oracle 7, then there is no correlation between index-use and sorting using the upper() function. It's what is in your WHERE clause that affects the use of indices.

>The way we handle it is to store both the true column and also a copy in
>uppercase. We then index the uppercase column, and always make sure we order
>by it rather than the 'real' column. This is naturally invisible to the user.
>You have to maintain the uppercase column when the true column changes - in
>Oracle 7, you could use a database trigger for this purpose.
>
>Regards,
>
>Glenn Nicholas.
>
>In article <2ucuqv$kgm_at_toads.pgh.pa.us>
> prashant_at_cs.pitt.edu "The Peasant" writes:
>:We came across a problem (feature) which we need some insights into.
>:Oracle seems to differenciate (rightly so) between upper and lower case
>:when we are performing sort on a char field.
>:The thing we want to accomplish is for oracle to not-differenciate
>:between 'A' and 'a'etc...
>:so when we query the data sorted on say title we would like to see the
>:following
>... deleted
Received on Fri Jul 01 1994 - 23:33:28 CEST