tsql - How do I sort a VARCHAR column in SQL server that contains numbers?

Question

I have a VARCHAR column in a SQL Server 2000 database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer.

When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or letters and numbers (such as 'A1') can be sorted alphabetically as normal. For example, this would be an acceptable sort order.

1
2
10
A
B
B1

What is the best way to achieve this?

Answer 1

One possible solution is to pad the numeric values with a character in front so that all are of the same string length.

Here is an example using that approach:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

The 100 should be replaced with the actual length of that column.