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

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

Leave a Comment