sql order by - SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12

Question

I’m attempting to order by a number column in my database which has values 1-999

When I use

ORDER_BY registration_no ASC

I get….

1
101
102
103
104
105
106
107
108
109
11
110
Etc…

So it appears to be ordering by the first digit as oppose to the number.

Does anyone know what SQL to use if I want to order this by value? So 1,2,3,4,5,6 etc

Answer 1

One way to order by positive integers, when they are stored as varchar, is to order by the length first and then the value:

order by len(registration_no), registration_no

This is particularly useful when the column might contain non-numeric values.

Note: in some databases, the function to get the length of a string might be called length() instead of len().