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}
That's not a natural sort, that's an ASCII sort.
Malfist
2010-07-01 14:55:28
A:
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.
ktharsis
2010-07-01 14:58:45
The problem with the union is that this sort isn't the first level sort. The first sort is by Property Name, and this sort is by Unit Number (typically numeric but could be A,B,C,...) Unioning would throw a lot off
Malfist
2010-07-01 15:13:12
+4
A:
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...
DiGi
2010-07-01 15:19:42
Almost there, that doesn't sort non-numeric it just adds them willy-nilly to the end. I'll modify your query and accept it
Malfist
2010-07-01 15:30:42