如何使用SQL对两个NVARCHAR列实现类似整数的ORDER BY排序？

嘿，这个问题我之前也帮不少人解决过——字符串类型的列带数字后缀时，默认的字典序排序确实会坑人，比如TW001_10会排在TW001_2前面，这显然不是你想要的整数逻辑排序对吧？

核心思路是把每个列拆分成前缀字符串和后缀数字两部分，先按前缀的字典序排序，再把后缀转换成整数按数值大小排序。下面针对SQL Server（你用NVARCHAR大概率是这个环境）给出具体实现：

基础实现方案（兼容大部分SQL Server版本） #

假设你的表名为your_table，直接在ORDER BY里拆分列即可：

SELECT column1, column2
FROM your_table
ORDER BY
    -- 处理column1：先按前缀排序，再按后缀数字升序
    LEFT(column1, CHARINDEX('_', column1) - 1),
    CAST(RIGHT(column1, LEN(column1) - CHARINDEX('_', column1)) AS INT),
    -- 处理column2：同理
    LEFT(column2, CHARINDEX('_', column2) - 1),
    CAST(RIGHT(column2, LEN(column2) - CHARINDEX('_', column2)) AS INT) ASC;

代码解释： #

• CHARINDEX('_', column1)：找到下划线在字符串中的位置
• LEFT(column1, ... -1)：提取下划线前面的前缀部分（比如TW001_1提取出TW001）
• RIGHT(column1, ...)：提取下划线后面的数字部分，再用CAST(AS INT)转换成整数，这样就能按数值排序了

鲁棒性优化（处理无下划线的情况） #

如果你的列中可能存在没有下划线的记录，可以用CASE语句做兼容处理，避免报错：

SELECT column1, column2
FROM your_table
ORDER BY
    -- column1前缀：如果有下划线就取前缀，否则用原字符串
    CASE 
        WHEN CHARINDEX('_', column1) > 0 THEN LEFT(column1, CHARINDEX('_', column1) - 1)
        ELSE column1
    END,
    -- column1数字：如果有下划线就转成整数，否则设为0（或按需求调整）
    CASE 
        WHEN CHARINDEX('_', column1) > 0 THEN CAST(RIGHT(column1, LEN(column1) - CHARINDEX('_', column1)) AS INT)
        ELSE 0
    END,
    -- column2同理
    CASE 
        WHEN CHARINDEX('_', column2) > 0 THEN LEFT(column2, CHARINDEX('_', column2) - 1)
        ELSE column2
    END,
    CASE 
        WHEN CHARINDEX('_', column2) > 0 THEN CAST(RIGHT(column2, LEN(column2) - CHARINDEX('_', column2)) AS INT)
        ELSE 0
    END ASC;

高版本SQL Server简化写法（2016+） #

如果你的SQL Server版本是2016及以上，可以用STRING_SPLIT配合CROSS APPLY来拆分，代码可读性更好：

SELECT t.column1, t.column2
FROM your_table t
-- 拆分column1的前缀和数字
CROSS APPLY (
    SELECT value AS prefix, LEAD(value) OVER(ORDER BY ordinal) AS num
    FROM STRING_SPLIT(t.column1, '_', 1) -- 第三个参数1保留拆分顺序
) ca1
-- 拆分column2的前缀和数字
CROSS APPLY (
    SELECT value AS prefix, LEAD(value) OVER(ORDER BY ordinal) AS num
    FROM STRING_SPLIT(t.column2, '_', 1)
) ca2
ORDER BY 
    ca1.prefix, CAST(ca1.num AS INT),
    ca2.prefix, CAST(ca2.num AS INT) ASC;

这样就能完美实现你想要的类整数排序效果啦！

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