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().

Answer 2

ORDER_BY cast(registration_no as unsigned) ASC

explicitly converts the value to a number. Another possibility to achieve the same would be

ORDER_BY registration_no + 0 ASC

which will force an implicit conversation.

Actually you should check the table definition and change it. You can change the data type to int like this

ALTER TABLE your_table MODIFY COLUMN registration_no int;

That way indexes can be used properly and the order by won't slow down the query.

Answer 3

If you are using SQL Server:

ORDER_BY cast(registration_no as int) ASC

Answer 4

ORDER_BY cast(registration_no as unsigned) ASC

gives the desired result with warnings.

Hence, better to go for

ORDER_BY registration_no + 0 ASC

for a clean result without any SQL warnings.