如何按字母表顺序对可能包含数字的varchar字段进行排序？

Question

我相信这应该是一个很普遍的问题，所以我猜Microsoft已经解决了这个问题。我的谷歌搜索技能还不够好。我有一个我想排序的字段，它是一个varchar字段，例如：

• Q
• Num 10
• Num 1
• A
• Num 9
• Num 2
• F

现在我希望结果是：

• A
• F
• Num 1
• Num 2
• Num 9
• Num 10
• Q

但实际上并不是这样的（请注意，Num 10出现在Num 1之后，而不是像预期的那样在Num 9之后）。

• A
• F
• Num 1
• Num 10
• Num 2
• Num 9
• Q

现在我知道原因，所以你不需要解释 :)但我记不起如何解决它或是否有一个好的标志或命令可以用来解决它。

编辑：

上面的示例只是一个示例。该列可能包含任何值。任何字母和数字的组合。有没有一种方法可以按人类字母顺序排序，而不是按ASCII值字母顺序排序？

编辑2： 感谢迄今为止的答案。我在谈论任意数据。如果它在固定位置或前面有什么东西，那么这很容易，我也不会问。我正在寻求解决此问题的一般解决方案，其中包含任意数据。没有模式，没有规则，什么都没有。

- uriDium

---

数字值是否总是以文本“Num”为前缀？ - Josh Anderson

重新编辑2：在某些情况下可能存在一般解决方案，但我不知道任何严格定义“自然排序”的方法。你期望它表现如何？提供更复杂的用例将会有很大帮助。我已经编写了一个CLR函数，似乎对我的需求工作得很好，请参见我下面的编辑。 - D'Arcy Rittich

@OrbMan。我真的希望有类似于SELECT * FROM x ORDER BY (NaturalIndex(col))或类似的东西。我认为这个问题可能已经被SQL Server解决了，只是我不知道这个函数。我将使用Yada发布的链接在C#中实现我的解决方案。无论如何，还是谢谢。 - uriDium

Answer 1

如果字段总是以数字结尾，可能有一个单词在它之前，并且在它之前有一个空格，您可以使用CHARINDEX/SUBSTRING来解决这个问题。

以下是一个示例：

select *
from (
    select 'Q' x
    union
    select 'Num 10'
    union
    select 'Num 1'
    union
    select 'A'
    union
    select 'Num 9'
    union
    select 'Num 2'
    union
    select 'F'
) a
order by
    case
        when CHARINDEX(' ', x) <> 0 then LEFT(x, CHARINDEX(' ', x) - 1)
        else x
    end,
    cast(case
        when CHARINDEX(' ', x) <> 0 then substring(x, CHARINDEX(' ', x) + 1, LEN(x) - CHARINDEX(' ', x) )
        else ''
    end as int)

这个的输出是：

A
F
Num 1
Num 2
Num 9
Num 10
Q

编辑：

由于您的数据不够一致，无法使用硬编码方法，因此解决方案需要采取更激进的措施。我尝试过基于 T-SQL 的函数来实现自然排序，但发现它们速度太慢，无法使用。相反，我编写了一个基于 CLR 的函数，它的性能非常好。该函数返回一个标量值，您可以对其进行排序。您可以在这里找到代码和安装说明。

- D'Arcy Rittich

Answer 2

“我正在寻求一个适用于任何任意数据的通用解决方案。没有模式、规则或其他限制。”

编程的问题在于找到模式，从中导出规则，并根据那些规则应用解决方案。因此，如果没有这些先决条件，你的问题就相当棘手。

基本上，你需要将排序字符串分成纯字母块和纯数字块的标记，并对每个类别应用不同的排序顺序。只要有一种模式，这是可行的。

   AAA999AA
   A9AAAAA
   A999A

但是每个模式都需要定制的解决方案，这需求太大了。对于任意数据排列的通用解决方案是一个巨大的挑战。

- APC

Answer 3

这是 ASCII 排序顺序与自然排序顺序的古老问题。

请参见 人类可读的排序 : 自然排序顺序获取更多详细信息。

- Yada

---

我将把这个C++ http://www.stereopsis.com/strcmp4humans.html 翻译成C#，并在C#中处理结果集。我本来希望能够在SQL Server中找到一个简单的方法来实现，但我觉得可能没有这样的方法。 - uriDium

Answer 4

你添加了

该列可以包含任何值。任何字母和数字的组合。

那么，你想在哪里使用例如“foo1bar”和“foo10bar”，或者“foo10bar11”和“foo10bar1”？或者“Foo Two”和“Foo Three”？

如果没有有意义的数据，就不会有明智的解决方案。你有随机数据。请定义“可读性强的”。

- gbn