Sort a VARCHAR column in SQL server that contains numbers?

I have a VARCHAR column in a SQL Server 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.

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


In the example below, 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.
SELECT
 ...
ORDER BY
  CASE 
    WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value) 
    ELSE 9999999 -- or something huge
  END,
  value


SQL TSQL Sort Varchar Int


Back To Top
© 1998 - 2024 psacake.com
Version 7.21 | Advertise on this site