Oracle - Sorting a VARCHAR2 field like a NUMBER - I found a solution, need explanation on it

Question

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 it work, can anyone explain?

Answer 1

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