Search code examples
sqlsql-serversql-order-by

Sql Server query varchar data sort like int

I have one table like 

CREATE TABLE table_name
(
P_Id int,
amount varchar(50)
)

Data Like 

Id amount
----------
1 2340
2 4568
3 10000

Now I want to sort table by amount but one problem is amount is varchar so it sort table like this 

Id amount
----------
3 10000
1 2340
2 4568

but i want result like this 

Id amount
----------
3 10000
2 4568
1 2340

what should i do ?

Solution

  • Cast amount column into Numeric in ORDER BY clause while selecting:

    SELECT * FROM MyTable
ORDER BY CAST(amount AS Numeric(10,0)) DESC

    You can change the decimal point value according to your requirement to get more precise result:

    SELECT * FROM MyTable
ORDER BY CAST(amount AS Numeric(10,2)) DESC
                                   ^

    Result:

    Id amount
    3 10000
    2 4568
    1 2340

    See this dbfiddle

    Note: As @Ice suggested, this will fail if the amount field contains non numeric data like ab123 or xyz (obviously).