如何修改SQL语句实现varchar数据先字母后数字排序（含附加字符串）

问题背景 #

数据存储在varchar类型的DistrictName列中，示例数据如下：

FORT BEND COUNTY MUD 2
FORT BEND COUNTY MUD 23
FORT BEND COUNTY MUD 138
FORT BEND COUNTY MUD 256
FORT BEND COUNTY MUD 250A
FORT BEND COUNTY MUD 23 JT FAC
FORT BEND COUNTY MUD 169 MASTER
FORT BEND COUNTY MUD 214 INTERNAL
FORT BEND COUNTY MUD 122 123 JT FAC
FORT BEND COUNTY MUD 169 - INTERNAL
FORT BEND COUNTY MUD 188 JT Sewer Plant
FORT BEND COUNTY MUD 188 JT Water Plant
FORT BEND COUNTY MUD 214 MASTER DISTRICT

期望排序规则：

• 先按名称前缀（如FORT BEND COUNTY MUD）分组排序
• 数字部分按数值大小排序，且带附加字符串的项（如23 JT FAC）排在对应纯数字项（23）的下方

原SQL语句无法满足带附加字符串的排序需求：

SELECT DistrictName
FROM Master
ORDER BY LEFT(DistrictName, PATINDEX('%[0-9]%', DistrictName + '0') - 1), LEN(DistrictName), DistrictName

修改后的SQL语句 #

SELECT DistrictName
FROM Master
ORDER BY 
    -- 按数字前的名称前缀分组排序
    LEFT(DistrictName, PATINDEX('%[0-9]%', DistrictName + '0') - 1),
    -- 提取数字部分转为数值，实现按数字大小排序
    CAST(
        SUBSTRING(
            DistrictName,
            PATINDEX('%[0-9]%', DistrictName),
            PATINDEX('%[^0-9]%', SUBSTRING(DistrictName, PATINDEX('%[0-9]%', DistrictName), LEN(DistrictName)) + ' ') - 1
        ) AS INT
    ),
    -- 标记纯数字项为0，带附加字符串的为1，确保纯数字项优先
    CASE 
        WHEN PATINDEX('%[^0-9 ]%', SUBSTRING(DistrictName, PATINDEX('%[0-9]%', DistrictName), LEN(DistrictName))) = 0 
        THEN 0 
        ELSE 1 
    END,
    -- 同组内按完整名称做最后排序
    DistrictName

语句说明 #

1. 前缀分组：保留原逻辑，提取数字出现前的文本，确保同前缀的项归为一组。
2. 数值排序：通过嵌套的PATINDEX和SUBSTRING精准提取数字部分，转换为INT类型后按数值排序，解决字符串数字（如2和23）的排序误差。
3. 纯数字项优先：用CASE判断数字后是否存在非数字、非空格的内容，给纯数字项更低的排序权重，让其排在对应组的最前面。
4. 最终补序：同前缀同数字的项，按完整名称的字典序排序，保证附加字符串的项有序排列。

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