SQL ORDER chars numerically

Question

I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:

100
131
200
21
30
31000
etc.

How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?

Thank You.

Answer 1

Try this:

ORDER BY CAST(thecolumn AS int)

Answer 2

This is an issue with ordering numeric strings in a "natural sort" (if you lookup "natural sorting" on Google you'll find tons of stuff). Essentially casting the string as int and sorting on the resulting value should fix it.

Answer 3

The reason for this is that with a char data type, you are sorting the rows as a string.

The idea to ORER BY CAST() is correct, however performance of this will go down as the number of returned results increases.

If it's only numerical data in this column, the best practice would be to find a suitable numerical data type and change it.

If you really can't change the column and you find yourself having performance issues, I suggest having a sort order column which contains the value cast to an integer (will nulls converted to an appropriate value). Index the sort order column and ideally, add a trigger to the char column so that inserts or updates to the char value trigger an update to the integer value.