如何在SQL Server中实现高级字母数字字符串的正确排序

期望排序结果 #

1A1
1A2
1A3
1A10
1A14
1A15
1A16
1A141
1A149
1A150
2A1
3B1
4C3
4C4
7E1
9999A7777

原代码及异常结果 #

原排序代码：

SELECT [failure_mode_code]
FROM dst.Failure_mode
ORDER BY
    CAST(LEFT([failure_mode_code], PATINDEX('%[A-Z]%', [failure_mode_code] + 'X') - 1) AS INT),
    SUBSTRING([failure_mode_code], PATINDEX('%[A-Z]%', [failure_mode_code] + 'X'), LEN([failure_mode_code])),
    [failure_mode_code]

得到的异常排序结果：

1A10
1A1
1A14
1A141
1A149
1A15
1A150
1A16
1A2

问题原因 #

原代码的第二排序项是将字母及后续的整个字符串按字典序排序，而非数字自然序。例如字符串A10和A1比较时，字典序规则会让A10排在A1之前，导致数字部分的排序不符合预期。

修正后的代码 #

我们需要将字符串拆分为「前缀数字」「中间字母」「后缀数字」三个部分，分别按数字、字母、数字的逻辑排序：

SELECT [failure_mode_code]
FROM dst.Failure_mode
ORDER BY
    -- 按前缀数字升序（转为整数排序）
    CAST(LEFT([failure_mode_code], PATINDEX('%[A-Z]%', [failure_mode_code]) - 1) AS INT),
    -- 按中间字母升序
    SUBSTRING([failure_mode_code], PATINDEX('%[A-Z]%', [failure_mode_code]), 1),
    -- 按后缀数字升序（转为整数排序）
    CAST(SUBSTRING([failure_mode_code], PATINDEX('%[A-Z]%', [failure_mode_code]) + 1, LEN([failure_mode_code])) AS INT)

代码说明 #

1. PATINDEX('%[A-Z]%', [failure_mode_code])：定位字符串中第一个字母的位置，以此拆分前缀数字和后续部分。
2. 前缀数字转为整数后排序，避免字典序导致的数字排序异常。
3. 中间字母直接按字典序排序，符合常规需求。
4. 后缀数字同样转为整数排序，确保1 < 2 < 10 < 14这样的自然序逻辑。

内容的提问来源于stack exchange，提问作者SUSHANTH SANJU