Sql Server query varchar data sort like int

Question

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 ?

You have a design flaw in your database. You should use the correct datatype for the data. The earlier you fix this the less effort you will have to make afterwards to get around it. — Nick.Mc, May 30 '13 at 06:51
You can use cast or convert in your order by clause but better to change the data type — Elmer, May 30 '13 at 08:05
It is mistake but now i can't change table design so. . i work with varchar only. — Archit, May 30 '13 at 09:12

Himanshu · Accepted Answer · 2022-04-28T08:05:55.803

30

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

edited Apr 28 '22 at 08:05

answered May 30 '13 at 06:47

Himanshu

31,810
31
111
133

3

Be carefull, statement will fail if Amount cannot be converted to a numeric value. This will also happen with nice values like 10.5 or 10,5 depending on some country configurations. – Ice May 30 '13 at 21:11
@Ice: That's obvious. If the amount field values like `ab123` or `xyz` it will fail and there is no point of ordering in that case. Reg. decimal values, we can add decimal points according to our requirement. E.g. `AS Numeric(10,2)` instead of `AS Numeric(10,0)`. – Himanshu Apr 28 '22 at 07:57

aizaz · Answer 2 · 2013-05-30T07:11:39.587

17

Try ABS():

SELECT * FROM MyTable ORDER BY ABS(MyCol) DESC;

SQL Fiddle

edited May 30 '13 at 07:11

answered May 30 '13 at 06:55

aizaz

3,056
9
25
57

This worked, the chosen answer did not. Could this be a version issue? `ERROR 1064 (42000): You have an error in your SQL syntax...near 'Numeric(10,0)) desc limit 10' at line 1` – Mageician May 30 '19 at 18:45
Thanks, it solved my issue or sorting varchar as number (INT) – Tayyab Hayat May 01 '20 at 10:55

score 2 · Answer 3 · answered Dec 22 '16 at 12:50

Try this

SELECT * FROM #varchar_field ORDER BY CASE WHEN ISNUMERIC(mixed_field) = 1 THEN CAST(mixed_field AS FLOAT) WHEN ISNUMERIC(LEFT(mixed_field,1)) = 0 THEN ASCII(LEFT(LOWER(mixed_field),1)) ELSE 2147483647 END

Ref: http://sqlserverplanet.com/tsql/how-to-order-numeric-values-in-a-varchar-field

Sql Server query varchar data sort like int

3 Answers3

Linked