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

There are a few possible ways to do this.

One would be

SELECT
 ...
ORDER BY
  CASE 
    WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value) 
    ELSE 9999999 -- or something huge
  END,
  value

the first part of the ORDER BY converts everything to an int (with a huge value for non-numerics, to sort last) then the last part takes care of alphabetics.

Note that the performance of this query is probably at least moderately ghastly on large amounts of data.

Answer 2

SELECT FIELD FROM TABLE
ORDER BY 
  isnumeric(FIELD) desc, 
  CASE ISNUMERIC(test) 
    WHEN 1 THEN CAST(CAST(test AS MONEY) AS INT)
    ELSE NULL 
  END,
  FIELD

As per this link you need to cast to MONEY then INT to avoid ordering '$' as a number.

Answer 3

select
  Field1, Field2...
from
  Table1
order by
  isnumeric(Field1) desc,
  case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end,
  Field1

This will return values in the order you gave in your question.

Performance won't be too great with all that casting going on, so another approach is to add another column to the table in which you store an integer copy of the data and then sort by that first and then the column in question. This will obviously require some changes to the logic that inserts or updates data in the table, to populate both columns. Either that, or put a trigger on the table to populate the second column whenever data is inserted or updated.

Answer 4

This seems to work:

select your_column
from your_table
order by
case when isnumeric(your_column) = 1 then your_column else 999999999 end,
your_column


Answer 5

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 that uses this approach:

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

The actual lenght of the column should be used instead of 100 in the example above.

Answer 6

also note that IsNumeric returns 1 for values 24e4 and 12d34