SQL Order By using columns of Strings that may be numbers…

Had a column of type string that may be numbers that are entered by the user of an application. The user is allowed to sort, but using the standard “order by colname asc” can give this nasty list:

1
12
19
3
4
44
7
acorns
beer

Changing the command to read “order by cast(colname as signed) asc” makes the list much prettier:

1
3
4
7
12
19
44
acorns
beer

This syntax was using MySQL, but should port nicely elsewhere.

Leave a Reply

Your email address will not be published. Required fields are marked *