Oracle - Sorting a VARCHAR2 field like a NUMBER - I found a solution, need explanation on it
I have a VARCHAR2 column that I want to sort numerically. 99% (or possibly even 100%) of the time it will contain numbers. I was looking around and found this solution. Quoting the source:
Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.
For example,
select * from supplier order by lpad(supplier_id, 10);
This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.
I've played around a little bit with this solution and it seems to be workign (so far), but how does i开发者_JAVA百科t work, can anyone explain?
When sorting strings/varchar, the field is always serted from left to right, like you would sort normal words.
That is why you have problems when sorting
1
2
3
10
11
20
which would be sorted as
1
10
11
2
20
3
But, now if you pad the values left, you will have something like
001
002
003
010
011
020
which would sort correctly
精彩评论