Sql Server中order by对varchar类型排序结果不对

1、问题描述

我写一个sql想要把查询结果根据LineNumber升序进行排序，即1.0，1.1，1.2，...1.3.2....2.0，......10.0，......15.2，......这样子进行排序的。但是排序结果总是不对。

select POID, LineNumber from Business..PurchaseOrderItem where POID=131089 order by LineNumber asc

2、产生原因

LineNumber字段是varchar类型的，varchar比较是先比较首个字符的ascii码的大小，然后再依次比较其他的字符的ascii码。

比如对于"10.0"和"2.0"，"10.0"的首个字符为"1"，"2.0"的首个字符为"2"。"1"<"2"，因此"10.0"是比"2.0"要小，"10.0"是排在"2.0"前面的。

3、解决方法

把varchar转为数字类型进行比较。因为LineNumber会有像"6.3.2"这种是无法直接转为数字的。所以我这里就将第一个小数点左侧的转为数字。先比较第一个小数点左侧的数字，再比较整体。

--根据LineNumber进行升序排序
select POID, LineNumber from Business..PurchaseOrderItem where POID=131089 
order by 
case when SubString (LineNumber, 0, CharIndex ('.', LineNumber)) ='' then
 Cast(LineNumber as bigint) 
else 
Cast(SubString (LineNumber, 0, CharIndex ('.', LineNumber)) as bigint)
 end ,
--LineNumber为空或者null的，放在最后
case when LineNumber='' or LineNumber is null then 1 else 0 end asc,LineNumber asc

解释一下这段代码的意思：因为LineNumber可能没有小数点，比如LineNumber=12，因此 SubString (LineNumber, 0, CharIndex ('.', LineNumber))取第一个小数点左侧数字就为空了。因此我就直接把LineNumber转为数字类型进行排序比较。