More Fun With ORDER BY

Ever tried ordering a character field which has data like the following?

Unless you convert the field to a numeric data type, the records returned by a simple ORDER BY MyField are not going to be in numeric order, because the ASCII for '2' is less than the ASCII for '3', so '221' will
come before '32'.

There is another solution:

SELECT MyField FROM MyTable
ORDER BY RIGHT('000000'+LTRIM(MyField),6)

Here I have assumed that none of the numbers will be more than 6 digits long.

OK, so what if some (but not all) of these numbers have letters after them:

The answer again, is a convoluted ORDER BY expression:

SELECT MyField FROM MyTable
ORDER BY CASE
WHEN RIGHT(MyField,1) BETWEEN '0' AND '9'
THEN RIGHT('000000'+LTRIM(MyField),6)+'0'
ELSE RIGHT('000000'+LTRIM(MyField),6) END

To test this type of expression, it's useful to put it in the SELECT clause as well.