SQL Server Order By Asc In a varchar column storing mostly number

Question

I have a VarChar column in SQL Server in which I am storing mostly numbers but there are also some strings. I wanted to use

Select column1  
from table 
Order by Column1 Asc

but it sorts the numbers in this order instead

1
10
11
12
19
2
3
4
5
String1
String2
...

I have searched for a way to do this online and the recommended method was to cast as INT or do:

Select column1 
from table 
Order by Column1 + 0 Asc

However, those solutions does not work for me because I have strings in my column and I get the error:

Conversion failed when converting the varchar value 'Damage' to data type int.

You likely oversimplify your issue - and any solution will depend greatly on your desired order (which you left out of your description) and the variety of values in your column. Consider what should happen when the column contains a value like "009". Does that sort before or after "1"? How about "1A" or "1.1"? — SMor, Aug 16 '20 at 01:09
If you properly store numbers as numbers, this won't be an issue. If you're storing strings, you can left-pad them with zeros to make the sort work properly - for example, `1` should be stored as `01` if the value can only be two digits, `001` if it can be three, etc. — Ken White, Aug 16 '20 at 05:39
What are "numbers"? `9876543210` doesn't fit in an `int`. `8BADF00D` or `0xDEADBEEF`? `3.1415`? `42.000,00`? `6.02E+23`? `67,890,00,00,00,000` ([Indian numbering system](https://en.wikipedia.org/wiki/Indian_numbering_system)? `Pi`? `66-8i+2j-7k` ([Quaternion](https://en.wikipedia.org/wiki/Quaternion)? Is `(666)` negative? How do you handle currency symbols? — HABO, Aug 16 '20 at 13:00

Vahid Farahmandian · Accepted Answer · 2020-08-16T07:11:52.373

Suppose that you have a table like this:

CREATE TABLE #y(i VARCHAR(10))

and you have inserted the following data in it:

INSERT INTO #y VALUES('1'),('2'),('10'),('9'),('20'),('ffff'),('aaaa'),('zzzz')

You can try the following query to sort the data properly as you need:

SELECT * FROM #y 
ORDER BY CASE ISNUMERIC(i) WHEN 1 THEN CAST(i AS INT) ELSE 99999999999 end ASC, i ASC

You can find many other ways to solve this problem here too.

Read more about ISNUMERIC, CAST and CASE

1 Answers1