MS SQL 2005/2008中如何正确排序字母数字型Ordernumber字段？

问题原因分析 #

你之前的两个SQL语句报错核心原因一致：CASE表达式的不同分支返回了不同数据类型（一个是INT，一个是VARCHAR）。SQL Server会自动将所有分支结果隐式转换为优先级更高的INT类型，导致包含字母的字符串（比如4a）转换为INT时失败。

正确解决方案 #

我们可以把Ordernumber拆分为数字前缀部分和字母后缀部分，先按数字前缀排序，再按字母后缀排序，就能得到你想要的顺序：

SELECT Ordernumber
FROM alphanumericorder
ORDER BY
    -- 提取数字前缀并转换为INT，用于数值排序
    CAST(
        SUBSTRING(Ordernumber, 1, 
            CASE 
                WHEN PATINDEX('%[^0-9]%', Ordernumber) = 0 THEN LEN(Ordernumber)
                ELSE PATINDEX('%[^0-9]%', Ordernumber) - 1
            END
        ) AS INT
    ),
    -- 提取字母后缀（无后缀则为空），用于字母顺序排序
    SUBSTRING(Ordernumber, 
        CASE 
            WHEN PATINDEX('%[^0-9]%', Ordernumber) = 0 THEN LEN(Ordernumber) + 1
            ELSE PATINDEX('%[^0-9]%', Ordernumber)
        END,
        LEN(Ordernumber)
    )

代码逻辑说明 #

1. 提取数字前缀：
   • PATINDEX('%[^0-9]%', Ordernumber) 找到第一个非数字字符的位置；
   • 如果返回0，说明该值全是数字，直接取整个字符串作为数字部分；
   • 否则截取从开头到非数字字符前一位的内容，转换为INT类型。
2. 提取字母后缀：
   • 如果全是数字，从字符串长度+1的位置截取（结果为空字符串）；
   • 否则从第一个非数字字符的位置开始截取，得到字母部分。

这样排序时，先按数字大小排序，数字相同的再按字母顺序排序，正好符合你需要的1, 2, 3, 4a, 4b, 5, 6, 7, 8a, 8b, 9, 10顺序。

内容的提问来源于stack exchange，提问作者Fadl Assaad