MSSQL字符串排序异常：如何实现a1,a2,a3,a10的自然排序？

当前name字段是字符串类型，MSSQL默认按字典序排序，所以会出现a1,a10,a2,a3的结果——因为逐个字符比较时，a10的第二个字符1的ASCII码小于a2的2，因此排在前面。

要实现a1,a2,a3,a10的预期排序，需要修改查询的ORDER BY子句，拆分字符串中的字母前缀和数字部分，对数字部分按数值类型排序，具体方案如下：

方案1：固定格式（字母+数字） #

如果所有name值都是「字母前缀+数字」的格式（比如a1、a10），可以用以下语句：

SELECT name 
FROM dto.test 
ORDER BY 
    -- 提取字母前缀部分排序
    LEFT(name, PATINDEX('%[0-9]%', name) - 1),
    -- 提取数字部分转为整数后排序
    CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), LEN(name)) AS INT) ASC

方案2：兼容混合格式 #

如果存在纯字母、纯数字的记录，或者前缀不固定，用带判断的排序逻辑兼容所有情况：

SELECT name 
FROM dto.test 
ORDER BY 
    CASE 
        WHEN PATINDEX('%[0-9]%', name) > 0 THEN LEFT(name, PATINDEX('%[0-9]%', name) - 1) 
        ELSE name 
    END,
    CASE 
        WHEN PATINDEX('%[0-9]%', name) > 0 THEN CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), LEN(name)) AS INT) 
        ELSE 0 
    END ASC

核心逻辑是：先按字母部分（或纯字符串）排序，再将数字部分转为整数类型排序，这样数字会按数值大小而非字典序排列。

内容的提问来源于stack exchange，提问作者mb-