Microsoft SQL 2005中的自然（人类字母数字）排序

Question

Microsoft SQL 2005中的自然（人类字母数字）排序

我们有一个大型数据库，我们有数据库端分页。这很快，在几分之一秒内从数百万条记录中返回50行的页面。

用户可以定义自己的排序，基本上选择要排序的列。列是动态的 - 一些具有数值，一些日期和一些文本。

虽然大多数按预期文本排序是愚蠢的。嗯，我说愚蠢，它对计算机有意义，但让用户感到沮丧。

例如，按字符串记录ID排序会产生如下内容：

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...等等。

我希望这个考虑到这个数字，所以：

rec1
rec2
rec3
rec4
rec10
rec14
rec20

我无法控制输入（否则我只是在前导000中格式化）而且我不能依赖单一格式 - 有些类似于“{alpha code} - {dept code} - {rec id}”。

我知道在C＃中有几种方法可以做到这一点，但是不能下拉所有记录来对它们进行排序，因为这样做会很慢。

有谁知道在Sql server中快速应用自然排序的方法？

---

我们正在使用：

ROW_NUMBER() over (order by {field name} asc)

然后我们就这样分页。

我们可以添加触发器，但我们不会。他们所有的输入都是参数化的，但是我无法改变格式 - 如果他们输入“rec2”和“rec10”，他们希望它们就像那样，以自然的顺序返回。

---

我们有有效的用户输入，遵循不同客户的不同格式。

有人可能会去rec1，rec2，rec3，... rec100，rec101

而另一个可能会去：grp1rec1，grp1rec2，... grp20rec300，grp20rec301

当我说我们无法控制输入时，我的意思是我们不能强迫用户更改这些标准 - 它们有一个像grp1rec1的值，我不能将其重新格式化为grp01rec001，因为这将改变用于查找和链接到外部系统。

这些格式变化很大，但通常是字母和数字的混合。

在C＃中对它们进行排序很容易 - 只需将其分解{ "grp", 20, "rec", 301 }，然后依次比较序列值。

但是，可能有数百万条记录并且数据被分页，我需要在SQL服务器上进行排序。

SQL服务器按值排序，而不是比较 - 在C＃中我可以将值拆分为比较，但在SQL中我需要一些逻辑（非常快）获得一致排序的单个值。

@moebius - 你的答案可能会有效，但是为所有这些文本值添加排序键确实感觉像是一个丑陋的妥协。

Answer 1

order by LEN(value), value不完美，但在很多情况下效果很好。

Answer 2

我知道这是一个老问题，但我刚刚遇到它，因为它没有得到一个公认的答案。我总是使用类似的方式：SELECT [Column] FROM [Table]ORDER BY RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))), 1000)唯一常见的问题是，如果您的列不会转换为VARCHAR（MAX），或者LEN（[Column]）> 1000（但如果您愿意，可以将1000更改为其他内容），但是可以根据您的需要使用这个粗略的想法。这也是比正常的ORDER BY [Column]更糟糕的性能，但它确实为你提供了OP中要求的结果。编辑：只是为了进一步说明，如果您有十进制值，例如有1，1.15和1.5（它们将排序为{1, 1.5, 1.15}），则上述将不起作用，因为这不是OP中要求的，但可以通过以下方式轻松完成：SELECT [Column] FROM [Table]ORDER BY REPLACE(RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))) + REPLICATE('0', 100 - CHARINDEX('.', REVERSE(LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX))))), 1)), 1000), '.', '0')结果： {1, 1.15, 1.5}而且仍然完全在SQL中。这不会对IP地址进行排序，因为您现在正在进入非常具体的数字组合，而不是简单的文本+数字。