sql-server - Microsoft SQL 2005 中的自然(人类字母数字)排序

Question

我们有一个大型数据库，在该数据库上有数据库端分页。速度很快，只需不到一秒的时间即可从数百万条记录中返回 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 服务器中快速应用自然排序的方法吗？

<小时/>

我们正在使用:

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 Server 上完成排序。

SQL Server 按值排序，而不是比较 - 在​​ C# 中，我可以将值拆分出来进行比较，但在 SQL 中，我需要一些逻辑来(非常快地)获得一致排序的单个值。

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

Answer 1

order by LEN(value), value

并不完美，但在很多情况下效果很好。