当对包含数字值的nvarchar列进行排序时,SQL Server返回错误的排序结果,我不知道为什么!是否有特定的方式让SQL Server对varchar列中包含数字值的进行排序呢?谢谢。
SELECT [nvarcharColumn]
FROM [Table]
ORDER BY CAST(ISNULL(NULLIF(ISNUMERIC([nvarcharColumn]),0),2147483647) as INT)
如果您想按数字排序,您需要将其转换为数字。
这里检查的是在 nVarChar
列中排序的数字列表:
With MyTable AS
(
Select Cast ('1' as nVarChar) nVarCharColumn UNION
Select '100' UNION
Select '99'
)
Select *
From MyTable
Order by 1
逻辑上来说,99应该在100之前,对吧?但是它是按照nVarChar排序的,所以100中的第一个1比第一个9小,因此它排在99之前。
nVarCharColumn
------------------------------
1
100
99
(3 row(s) affected)
With MyTable AS
(
Select Cast ('1' as nVarChar) nVarCharColumn UNION
Select '100' UNION
Select '99'
)
Select *
From MyTable
Order by CAST (nVarCharColumn as Int)
这是正确的输出
nVarCharColumn
------------------------------
1
99
100
(3 row(s) affected)
表格中出现了一个新情况,如果该列中还有字符串值呢?
;With MyTable AS
(
Select Cast ('1' as nVarChar) nVarCharColumn UNION
Select '100' UNION
Select '99' UNION
Select 'String Value'
)
Select *
From MyTable
Order by CAST (nVarCharColumn as Int)
Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the nvarchar value 'String Value' to data type int.
因此,SORT
部分检查 IsNumeric
- 在 SELECT
部分中不需要再执行相同的操作。
With MyTable AS
(
Select Cast ('1' as nVarChar) nVarCharColumn UNION
Select '100' UNION
Select '99' UNION
Select 'String Value'
)
Select *
From MyTable
Order by CAST ( Case When ISNUMERIC (nVarCharColumn)=1 Then nVarCharColumn Else -99 End as Int)
nVarCharColumn
------------------------------
String Value
1
99
100
(4 row(s) affected)
你可以将数字中的值进行类型转换,例如 int 或 float。
SELECT nVarCharValue FROM table ORDER BY CAST(nVarCharValue AS INT)
你的排序值应该转换为数字值。
ORDER BY CONVERT(INT, YourVarcharCol) ASC