How do I sort a VARCHAR column in SQL server that

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

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 2

SELECT *, CONVERT(int, your_column) AS your_column_int
FROM your_table
ORDER BY your_column_int

OR

SELECT *, CAST(your_column AS int) AS your_column_int
FROM your_table
ORDER BY your_column_int

Both are fairly portable I think.

Answer 3

This query is helpful for you. In this query, a column has data type varchar is arranged by good order.For example- In this column data are:- G1,G34,G10,G3. So, after running this query, you see the results: - G1,G10,G3,G34.

SELECT *,
       (CASE WHEN ISNUMERIC(column_name) = 1 THEN 0 ELSE 1 END) IsNum
FROM table_name 
ORDER BY IsNum, LEN(column_name), column_name;

Answer 4

 SELECT *,
       ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical
        FROM
            (

        SELECT '1'Id UNION ALL
        SELECT '25.20' Id UNION ALL

    SELECT 'A115' Id UNION ALL
    SELECT '2541' Id UNION ALL
    SELECT '571.50' Id UNION ALL
    SELECT '67' Id UNION ALL
    SELECT 'B48' Id UNION ALL
    SELECT '500' Id UNION ALL
    SELECT '147.54' Id UNION ALL
    SELECT 'A-100' Id
    )A

    ORDER BY 
    CASE WHEN ISNUMERIC (ID)=0                                /* alphabetical sort */ 
         THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0
                   THEN LEFT(Id,PATINDEX('%[0-9]%',Id))
                   ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1)
              END
    END DESC

Answer 5

I solved it in a very simple way writing this in the "order" part

ORDER BY (
sr.codice +0
)
ASC

This seems to work very well, in fact I had the following sorting:

16079   Customer X 
016082  Customer Y
16413   Customer Z

So the 0 in front of 16082 is considered correctly.

Answer 6

This may help you, I have tried this when i got the same issue.

SELECT * FROM tab ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);