Custom sort order in SQL Server mixed datatypes stored in a varchar

Question

I have this

declare @testtable table (test nvarchar(max))


insert into @testtable (test) values ('1.2.3')
insert into @testtable (test) values ('1.20.3')
insert into @testtable (test) values ('1.19.x')
insert into @testtable (test) values ('1.x.x')
insert into @testtable (test) values ('1.19.3')
insert into @testtable (test) values ('DEC09')
insert into @testtable (test) values ('Plutonium')
insert into @testtable (test) values ('dec09')
insert into @testtable (test) values ('N/A')
insert into @testtable (test) values ('MyTest20')
insert into @testtable (test) values ('20MyTest')
insert into @testtable (test) values ('1.4.18')
insert into @testtable (test) values ('1.4.168')

select * from @testtable
order by test asc;

which outputs

1.19.3
1.19.x
1.2.3
1.20.3
1.4.168
1.4.18
1.x.x
20MyTest
DEC09
dec09
MyTest20
N/A
Plutonium

but I would like the output order to be

1.2.3
1.4.18
1.4.168
1.19.3
1.19.x
1.20.3
1.x.x
20MyTest
DEC09
dec09
MyTest20
Plutonium
N/A

(note that N/A is "magic" and always largest, "version" (ex 1.2.3) always have 3 digits, although one or more digit may be char x to indicate "any digit" which should always be considered largest possible digit)

How do I accomplish this in SQL Server?

The versions of this is different, because the solution will be placed on different servers — Millerbean, Apr 23 '12 at 10:55
*How* different? What's the earliest version you want this run on? Please add the appropriate tag(s). — Andriy M, Apr 23 '12 at 13:09
This is called "natural sorting" or sometimes "alphanumeric sorting". Microsoft said [over here on the Azure feedback portal](https://feedback.azure.com/forums/908035-sql-server/suggestions/32899927-support-natural-sorting-digitsasnumbers-as-a-c) that they'll add proper support for it in SQL Server if they get enough votes for it. — Peter Aylett, Nov 19 '18 at 23:08

Mikael Eriksson · Accepted Answer · 2012-04-23T15:19:23.617

3

select TT.*
from @testtable as TT
order by case when TT.test = 'N/A' then 1 else 0 end,
         case when isnumeric(parsename(test, 3)+'E+00') = 1 then cast(parsename(test, 3) as int) else 99999 end,
         case when isnumeric(parsename(test, 2)+'E+00') = 1 then cast(parsename(test, 2) as int) else 99999 end,
         case when isnumeric(parsename(test, 1)+'E+00') = 1 then cast(parsename(test, 1) as int) else 99999 end,
         test

edited Apr 23 '12 at 15:19

answered Apr 23 '12 at 09:43

Mikael Eriksson

136,425
22
210
281

1

Great solution, one thing however is that the column is a varchar and will be sorted as a varchar. You might consider using this solution to get this done: http://stackoverflow.com/a/119842/326923 – Geert Immerzeel Apr 23 '12 at 10:54
This give me the same result as my own posted above – Millerbean Apr 23 '12 at 12:30
1.4.168 needs to swap with 1.4.18, so still not 100 % :-) – Millerbean Apr 23 '12 at 12:39

score 0 · Answer 2 · answered Apr 23 '12 at 15:27

This should be easy.

Create a new table with 2 columns:

OrderValue string

Descr String

Place the values in the order you want

a- 1.2.3

b- 1.4.18

c- 1.4.168

d- 1.19.3

e- 1.19.x

f- 1.20.3

g- 1.x.x

h- 20MyTest

i- DEC09

j- dec09

k- MyTest20

l- Plutonium

m- N/A

Now join the testtable with this new table and order by "OrderValue"

That is it

Custom sort order in SQL Server mixed datatypes stored in a varchar

2 Answers2

Linked