CODEWITHSUNDEEP
sqlt-sql
Tim C
Tim C

Reputation: 70648

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

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?

Upvotes: 61

Views: 146912

Answers (14)

Dennis Xavier
Dennis Xavier

Reputation: 109

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);

Upvotes: 0

Yusuf
Yusuf

Reputation: 9

I have tried many of the answers provided above, none seem to work for me until i adjust the query this way - everything seems to be fine:

SELECT * FROM myTable ORDER BY CAST(myVacharColumn AS UNSIGNED) ASC

Upvotes: 0

Patrick Koorevaar
Patrick Koorevaar

Reputation: 1343

I combined some answers from here to this:

SELECT MyStringColumn,
       (CASE WHEN ISNUMERIC(MyStringColumn) = 1 THEN Cast(MyStringColumn as int) ELSE 999999999 END) MyNumericColumn 
FROM MyTable
ORDER BY MyNumericColumn, MyStringColumn

Upvotes: 0

Mikko
Mikko

Reputation: 29

The easiest and efficient way to get the job done is using TRY_CAST

SELECT my_column 
FROM my_table
WHERE <condition>
ORDER BY TRY_CAST(my_column AS NUMERIC) DESC

This will sort all numbers in descending order and push down all non numeric values

Upvotes: 0

Nitika Chopra
Nitika Chopra

Reputation: 1405

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;

Upvotes: 0

Param Yadav
Param Yadav

Reputation: 891

 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

Upvotes: -1

Bernhard
Bernhard

Reputation: 2809

you can always convert your varchar-column to bigint as integer might be too short...

select cast([yourvarchar] as BIGINT)

but you should always care for alpha characters

where ISNUMERIC([yourvarchar] +'e0') = 1

the +'e0' comes from http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber

this would lead to your statement

SELECT
  *
FROM
  Table
ORDER BY
   ISNUMERIC([yourvarchar] +'e0') DESC
 , LEN([yourvarchar]) ASC

the first sorting column will put numeric on top. the second sorts by length, so 10 will preceed 0001 (which is stupid?!)

this leads to the second version: 

SELECT
      *
    FROM
      Table
    ORDER BY
       ISNUMERIC([yourvarchar] +'e0') DESC
     , RIGHT('00000000000000000000'+[yourvarchar], 20) ASC

the second column now gets right padded with '0', so natural sorting puts integers with leading zeros (0,01,10,0100...) in correct order (correct!) - but all alphas would be enhanced with '0'-chars (performance)

so third version:

 SELECT
          *
        FROM
          Table
        ORDER BY
           ISNUMERIC([yourvarchar] +'e0') DESC
         , CASE WHEN ISNUMERIC([yourvarchar] +'e0') = 1
                THEN RIGHT('00000000000000000000' + [yourvarchar], 20) ASC
                ELSE LTRIM(RTRIM([yourvarchar]))
           END ASC

now numbers first get padded with '0'-chars (of course, the length 20 could be enhanced) - which sorts numbers right - and alphas only get trimmed

Upvotes: 5

Corey Trager
Corey Trager

Reputation: 23141

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

Upvotes: 2

Aleris
Aleris

Reputation: 8069

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.

Upvotes: 85

Orz
Orz

Reputation: 49

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.

Upvotes: 4

JohnB
JohnB

Reputation: 19012

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.

Upvotes: 5

Luke Bennett
Luke Bennett

Reputation: 32906

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.

Upvotes: 5

Matt Mitchell
Matt Mitchell

Reputation: 41861

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.

Upvotes: -1

Cowan
Cowan

Reputation: 37553

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.

Upvotes: 15

Related Questions