SQL Server sort by letters first then numbers

Question

I am trying for the past 2/3 hours to sort a varchar the way I want it, but I could not figure it out. Basically this is the query that comes the closest to what I want:

select Plantmaat
from Plant
Group by Plantmaat
order by (CASE WHEN Plantmaat like '[a-z]%' THEN 0 ELSE 1 END), Plantmaat ASC;

Outputs this:

LEV
PLG
S 10-12
S 12-14
S 14-16
S 16-18
UITS
10
-10
11
12
14
15
-9

But this is what I want to achieve:

LEV
PLG
UITS
S 10-12
S 12-14
S 14-16
S 16-18
-9
-10
10
11
12
14
15

AS you can see the biggest problem here is that it doesn't sort the row that has only letters, and that negative numbers end up at the bottom.

Ideally fix your design, don't store numerical data in a `varchar` column. — Thom A, Sep 29 '20 at 08:56
If you want you numbers in ascending order, why would you have `-9` before `-10`? Shouldn't it be the other way around? And why should a string starting with `U` be before a string starting with `S`? The main problem is, that the column you are sorting by is a varchar. So order is done lexiographically. You will also discover, that 100 will be sorted before 20 — derpirscher, Sep 29 '20 at 09:02

score 1 · Accepted Answer · answered Sep 29 '20 at 10:26

This seems to match your logic:

select *
from tab
order by
  case when col not like '%[0-9-]%' then 0 else 1 end -- no digits
 ,case when col like '[^0-9-]%' then 0 else 1 end     -- starting with non-digit
 ,case when col like '-%' then 0 else 1 end           -- negative values first
 ,len(col)                                            -- shorter (=smaller) values first
 ,col

See fiddle

score 0 · Answer 2 · answered Sep 29 '20 at 09:16

The best I can suggest is the following, however, it will not give you result you want:

ORDER BY CASE WHEN TRY_CONVERT(int,YourVarchar) IS NULL THEN 0 ELSE 1 END,
         CASE WHEN TRY_CONVERT(int,YourVarchar) IS NULL THEN YourVarchar ELSE NULL END
         TRY_CONVERT(int,YourVarchar);

As i mentioned, this won't give the results in your question, for the reasons derpirscher highlighted. For strings, 'U' has a "higher" value than 'S' and 'P' so will ordered after both of them, not after 'P' and before 'S'.

For numbers, the number -9 is larger than -10, and so will be ordered after it as well, not before.

As I mentioned, however, the real solution here is fix your design. If you are mixing both numerical and string data in the same column, and want numerical data to behave like a numerical value, then you need 2 columns; one for the string data one for the numerical data.

If, however, you don't want numerical data to behave like a numerical value (so '2' is "greater" than '10', and '4' + '7' is '47' ) then a varchar is fine, but you can't then expect it to ever act like a numerical value unless you change your design.

Thanks for your great explanation. I understand the way I want it sorted is pretty much impossible, but I wanted to give it a try. However, 2 seperate columns is not really an option for me unfortunately. Thanks again for your answer, I learned something again. — Lucdabomb, Sep 29 '20 at 09:40

score 0 · Answer 3 · answered Sep 29 '20 at 11:34

If you want the numbers in numeric order, then you want -10 before -9. If this is what you really want, then you can use:

select *
from tab
order by try_convert(int, col), col;

If you really do want -9 first, you can tweak this:

select *
from tab
order by sign(try_convert(int, col)),
         abs(try_convert(int, col)), col;

SQL Server sort by letters first then numbers

3 Answers3