SQL Server的自然排序方式? [英] Natural sort for SQL Server?

问题描述

我有一列通常只是数字(有时是字母，但这并不重要).

I have a column that is typically only numbers (sometimes it's letters, but that's not important).

如何使它自然排序?

当前排序如下:{1,10,11,12,2,3,4,5,6,7,8,9}

Currently sorts like this: {1,10,11,12,2,3,4,5,6,7,8,9}

我希望它这样排序:{1,2,3,4,5,6,7,8,9,10,11,12}

I want it to sort like this: {1,2,3,4,5,6,7,8,9,10,11,12}

推荐答案

IsNumeric被破坏"，ISNUMERIC(CHAR(13))返回1，CAST将失败.

IsNumeric is "broken", ISNUMERIC(CHAR(13)) returns 1 and CAST will fail.

使用ISNUMERIC(textval +'e0').最终代码:

Use ISNUMERIC(textval + 'e0'). Final code:

ORDER BY
  PropertyName,
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN 0 ELSE 1 END, -- letters after numbers
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN CAST(MixedField AS INT) ELSE 0 END,
  MixedField

您可以混合订购参数...

You can mix order parameters...

这篇关于SQL Server的自然排序方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！