存储在varchar中的SQL Server混合数据类型中的自定义排序顺序 [英] Custom sort order in SQL Server mixed datatypes stored in a varchar
本文介绍了存储在varchar中的SQL Server混合数据类型中的自定义排序顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个
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;
输出
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
但是我希望输出顺序是
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
(请注意,"N/A"是魔术"的,并且总是最大的,版本"(例如1.2.3)始终有3个数字,尽管一个或多个数字可能是char x以表示任何数字",该数字应始终为被认为是最大可能的数字)
(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)
如何在SQL Server中完成此操作?
How do I accomplish this in SQL Server?
推荐答案
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
这篇关于存储在varchar中的SQL Server混合数据类型中的自定义排序顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文