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

Advertisements
Advertisements

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.

Advertisements

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.