SQL ORDER chars numerically

问题:

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.

回答1:

Try this:

ORDER BY CAST(thecolumn AS int)

回答2:

This Worked for me:

ORDER BY ABS(column_name)

回答3:

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.

回答4:

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

The idea to ORDER 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.

回答5:

In some situations this might be a good match:

ORDER BY LENGTH(column_name), column_name

Especially if you have a column with either all digits or a mix of letters and digits but all with the same length.