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.

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

-- 
Glenn Nicholas, Concept             |   Internet: Glenn_at_wplace.demon.co.uk
Ph: +44 908 221313                  | Compuserve: 100070,3562