SQL Server中非规则字符串列的数字字母分离排序优化问题

问题分析 #

你的需求是对SQL Server中混合数字与字母的列（如'56LU20Q'、'56LU5Q'、'RW9'）按「数字-字母-数字」的分段规则排序，原实现因大量嵌套调用PATINDEX、TRY_PARSE且重复计算相同表达式，导致性能低下。

从示例结果看，排序逻辑为：

1. 先按开头的数字部分升序（无开头数字的项排最后）
2. 再按中间的字母部分升序
3. 最后按末尾的数字部分升序

优化方案 #

方案1：用CTE减少重复计算（即时查询优化） #

通过CTE预计算关键位置，避免重复调用字符串函数，大幅降低计算开销：

DROP TABLE IF EXISTS #Catalogs;
CREATE TABLE #Catalogs(CatalogNumber NVARCHAR(200))
INSERT INTO #Catalogs (CatalogNumber) VALUES('56LU20Q'),('56LU5Q'),('RW9');

WITH CatalogParts AS (
    SELECT 
        CatalogNumber,
        -- 定位第一个非数字字符的位置
        FirstNonDigitPos = PATINDEX('%[^0-9]%', CatalogNumber),
        -- 定位开头数字之后第一个数字的位置（用于拆分末尾数字）
        FirstDigitAfterLetterPos = PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber), ''))
    FROM #Catalogs
),
CatalogSortedFields AS (
    SELECT
        CatalogNumber,
        -- 前缀数字：开头有数字则转成BIGINT，无则设为大数（确保排最后）
        NumPrefix = CASE 
                        WHEN FirstNonDigitPos > 1 THEN TRY_CAST(LEFT(CatalogNumber, FirstNonDigitPos - 1) AS BIGINT)
                        ELSE 9999999999
                    END,
        -- 中间字母部分：从第一个非数字到下一个数字前的内容
        LetterPart = CASE
                        WHEN FirstDigitAfterLetterPos > 0 THEN LEFT(STUFF(CatalogNumber, 1, FirstNonDigitPos - 1, ''), FirstDigitAfterLetterPos - 1)
                        ELSE STUFF(CatalogNumber, 1, FirstNonDigitPos - 1, '')
                    END,
        -- 后缀数字：中间字母后的数字转成BIGINT，无则设为0
        NumSuffix = CASE
                        WHEN FirstDigitAfterLetterPos > 0 THEN TRY_CAST(SUBSTRING(STUFF(CatalogNumber, 1, FirstNonDigitPos - 1, ''), FirstDigitAfterLetterPos, LEN(CatalogNumber)) AS BIGINT)
                        ELSE 0
                    END
    FROM CatalogParts
)
SELECT CatalogNumber
FROM CatalogSortedFields
ORDER BY NumPrefix, LetterPart, NumSuffix;

方案2：持久化计算列+索引（高频查询优化） #

如果该排序是高频操作，建议将排序所需字段设为持久化计算列并创建复合索引，彻底避免每次查询时的计算开销：

-- 假设正式表名为Catalogs
ALTER TABLE Catalogs
ADD NumPrefix AS CASE 
                    WHEN PATINDEX('%[^0-9]%', CatalogNumber) > 1 THEN TRY_CAST(LEFT(CatalogNumber, PATINDEX('%[^0-9]%', CatalogNumber) - 1) AS BIGINT)
                    ELSE 9999999999
                END PERSISTED;

ALTER TABLE Catalogs
ADD LetterPart AS CASE
                    WHEN PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) > 0 
                    THEN LEFT(STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, ''), PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) - 1)
                    ELSE STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')
                END PERSISTED;

ALTER TABLE Catalogs
ADD NumSuffix AS CASE
                    WHEN PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) > 0 
                    THEN TRY_CAST(SUBSTRING(STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, ''), PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')), LEN(CatalogNumber)) AS BIGINT)
                    ELSE 0
                END PERSISTED;

-- 创建复合索引，包含原字段避免书签查找
CREATE NONCLUSTERED INDEX IX_Catalogs_Sort ON Catalogs(NumPrefix, LetterPart, NumSuffix) INCLUDE(CatalogNumber);

之后查询只需直接按计算列排序，性能会显著提升：

SELECT CatalogNumber
FROM Catalogs
ORDER BY NumPrefix, LetterPart, NumSuffix;

方案3：生成单一排序键字符串（简化排序逻辑） #

将分段内容转换为可直接排序的字符串（给数字补前导零，确保数字按数值排序），适用于不想创建额外列的场景：

WITH CatalogSortKey AS (
    SELECT 
        CatalogNumber,
        SortKey = 
            -- 前缀数字补前导零到10位，确保数值排序等价于字符串排序
            RIGHT('0000000000' + CASE WHEN PATINDEX('%[^0-9]%', CatalogNumber) > 1 THEN LEFT(CatalogNumber, PATINDEX('%[^0-9]%', CatalogNumber) - 1) ELSE '9999999999' END, 10)
            +
            -- 拼接中间字母部分
            CASE
                WHEN PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) > 0 
                THEN LEFT(STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, ''), PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) - 1)
                ELSE STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')
            END
            +
            -- 后缀数字补前导零到10位
            RIGHT('0000000000' + CASE
                                    WHEN PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')) > 0 
                                    THEN SUBSTRING(STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, ''), PATINDEX('%[0-9]%', STUFF(CatalogNumber, 1, PATINDEX('%[^0-9]%', CatalogNumber) - 1, '')), LEN(CatalogNumber))
                                    ELSE '0'
                                END, 10)
    FROM #Catalogs
)
SELECT CatalogNumber
FROM CatalogSortKey
ORDER BY SortKey;

关键优化点 #

• 替换TRY_PARSE为TRY_CAST：TRY_PARSE支持区域解析，性能远低于专门用于类型转换的TRY_CAST
• 避免重复计算：用CTE或子查询预计算关键位置，复用结果而非多次调用相同函数
• 持久化计算列：将排序所需字段提前计算并存储，配合索引实现极速排序

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