Natural sort for SQL Server?

Question

I have a column that is typically only numbers (sometimes it's letters, but that's not important).

How can I make it natural sort?

Currently sorts like this: {1,10,11,12,2,3,4,5,6,7,8,9}

I want it to sort like this: {1,2,3,4,5,6,7,8,9,10,11,12}

Answer 1

IsNumeric is "broken", ISNUMERIC(CHAR(13)) returns 1 and CAST will fail.

Use ISNUMERIC(textval + 'e0'). Final code:

ORDER BY
  PropertyName,
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN 0 ELSE 1 END, -- letters after numbers
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN CAST(MixedField AS INT) ELSE 0 END,
  MixedField

You can mix order parameters...

Answer 2

Cast it. Also, don't forget to use IsNumeric to make sure you only get the numbers back (if they include letters it IS important ;).

SELECT textval FROM tablename
WHERE IsNumeric(textval) = 1
ORDER BY CAST(textval as int)

Also, cast to the datatype that will hold the largest value.

If you need the non-numbers in the result set too then just append a UNION query where IsNumeric = 0 (order by whatever you want) either before or after.