Custom sort order in SQL Server mixed datatypes stored in a varchar
sorting, sql-server, sql-server-2008
Solution
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
Problem
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?