Custom sort order in mixed SQL Server data types stored in varchar

Question

I have it

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.xx 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.xx 20MyTest DEC09 dec09 MyTest20 Plutonium N/A 

(note that N / A is “magic” and always the largest, “version” (ex 1.2.3) always has 3 digits, although one or more digits can be char x to indicate “any digit” that should always be considered the largest possible number)

How to do this in SQL Server?

Answer 1

 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 

Answer 2

It should be easy.

Create a new table with two columns:

OrderValue String

Descr String

Put the values ​​in the desired order

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

h- 20MyTest

i- DEC09

j-dec09

k- MyTest20

l- Plutonium

m- N / A

Now join the test table with this new table and sort it with "OrderValue"

It's him