This question already has an answer here:
- Questions of string comparison in oracle 2 answers
I have an SQL statement that orders the table in ascending order via the id, but when I do this, the table shows it like:
c-10 c-12 c-12 c-24 c-5 c-6 c-80 c-800 c-8000
As this shows that the top few and bottom few are in order but not the other part. Why is this? As I have tried ordering by my other fields in the table and they appear fine, it's just this column. Do you think this could be an input error or something else?
Many thanks for your help!
The order is OK, sql sorts the strings in lexical order.
As 2 (in c-24) comes before 5 (in c-5) it is sorted like that.
If you want to sort stings like this in the numeric order prepend them with zeros.
c-0010 c-0012 c-0012 c-0024 c-0005 c-0006 c-0080 c-0800 c-8000 will be sorted as you want it.