对数据库进行嵌入排序

一位同事最近遇到了一个不寻常的问题，需要关心排序顺序。解释这个问题的最简单的方法是向你展示如下的代码。简单的将其粘贴到Query Analyzer（查询分析器）中然后在一个测试数据库上运行它：

create table TestSort(CodeToSortvarchar(10));
insert into TestSort(CodeToSort) values('a1');
insert into TestSort(CodeToSort) values('a2');
insert into TestSort(CodeToSort) values('a21');
insert into TestSort(CodeToSort) values('a10x');
insert into TestSort(CodeToSort) values('a2a');
insert into TestSort(CodeToSort) values('a10');

A sort on this column yields the following result set:

根据这一字段进行排序会得到如下的结果集：

select * from TestSort order by CodeToSort;
CodeToSort
a1
a10
a10x
a2
a21
a2a

然而应用程序的要求并不是那么简单。管理层人员想要按照下面这种方式进行排序：

a1
a2
a2a
a10
a10x
a21

因为其规则并不直观，下面我描述一下示例字符串：

·         第2个到第n个字符是数字。

·         数字字符后跟零个或者多个字母字符。

下面让我们对负责排序顺序的规则进行反向工程（示例中没有显式涉及规则4和5，但是我们可以推断出来）：

1. 第一个字符可以不予考虑。
2. 对字符串中的数字字符按照数字值排序。
3. 具有字母后缀的字符串，应该接在与其相似但没有字母后缀的字符串之后（也就是a10x应该接在a10之后）。
4. 字符串的数字部分可以是任何多位数字。
5. 对于具有两个字母后缀的字符串，对他们按照字典序排列（也就是a10x在a10y之前）。

我们所面临的最大问题是规则4。我们可以使用 SUBSTRING() 函数取到字符串的数字部分。相应地，我们必须取到所有连续的数字字符，将它们转成整数，然后按照转换成的整数值进行排序。