Oracle order by upper(colname) does not give proper results for non string columns

Question

When there is a non-string(i.e. : varchar,date) column(col1) in oracle db, if I do:

select * from table order by col1 asc

it orders properly. (ie. for date, it orders from oldest to latest, for numeric, from lowest to highest) But if i do, select * from table order by upper(col1) asc the ordering is not correct.

What is the cause for this behavior?

Answer 1

UPPER takes a string and returns a string. If col1 is anything other than a string, it will have to be implicitly cast to a string before the function is executed. Since the output of the UPPER function is a string, however, the sort will have to use string sorting semantics, not the sort semantics of col1. If col1 is numeric, for example

• UPPER(9) returns the string '9'
• UPPER(10) returns the string '10'

The string '9' comes alphabetically after the string '10' which is, presumably, the problem you're seeing.

But if col1 is not a string, why bother converting it to upper case in order to sort?