将nvarchar列排序为整数 [英] Sorting nvarchar column as integer

问题描述

我在nvarchar列中混合了数据(单词和数字). 这是按数字顺序对此列中的数据进行排序的最快方法.

I have mixed data i nvarchar column (words and numbers). Which is fastest way to sort data in this column in Numeric Order.

结果示例:

• 1
• 2
• 3
• ...
• 10
• 11
• ...
• aaaa
• aaab
• b
• ba
• ba
• ...

• 1
• 2
• 3
• ...
• 10
• 11
• ...
• aaaa
• aaab
• b
• ba
• ba
• ...

推荐答案

使用此:

ORDER BY
    CASE WHEN ISNUMERIC(column) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(column) = 1 THEN CAST(column AS INT) ELSE 0 END,
    column

这按预期工作.

注意:您说最快的方式.这条sql对我来说生成起来很快，但是执行计划显示了表扫描，然后进行了标量计算.这可能会产生一个临时结果，其中包含该列的所有值以及一些用于ISNUMERIC结果的额外临时列.执行起来可能并不快.

Note: You say fastest way. This sql was fast for me to produce, but the execution plan shows a table-scan, followed by a scalar computation. This could possibly produce a temporary result containing all the values of that column with some extra temporary columns for the ISNUMERIC results. It might not be fast to execute.

这篇关于将nvarchar列排序为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！