SQL Server 2016：批量替换公式字符串中的course_id为标识符

表结构 #

• courseassociation表：包含varchar类型的formula列，存储类似'123 and (586 or 309)'的布尔格式字符串，内容包含course_id（连续数字）、and/or运算符及括号
• inventory_course表：结构及示例数据如下：

需求 #

• 运行环境：兼容级别2016（130）的SQL Server
• 不可修改formula的字符串存储方式，需将其中的course_id替换为对应的course_identifier
• 查询需关联两张表
• 结果需包含两列：原formula、替换后的converted_formula，非数字内容保留原样
• 仅允许内存处理，可使用CTE，禁止创建表或临时表
• 连续数字视为一个course_id
• 结果示例：

现有实现（可接受括号前后加空格的情况） #

WITH RecursiveCTE AS 
(
    SELECT 
        REPLACE(REPLACE(ica.formula, ')', ' )'), '(', '( ') formula,
        CAST('' AS VARCHAR(MAX)) AS updated_formula,
        CAST(replace(replace(ica.formula, ')', ' )'), '(', '( ') + ' ' AS VARCHAR(MAX)) AS remaining_formula
    FROM 
        inventory_courseassociation ica
    UNION ALL
    SELECT 
        c.formula,
        CAST(updated_formula + CASE
                                   WHEN part NOT LIKE '%[^0-9]%' 
                                     THEN COALESCE((SELECT ic.course_identifier FROM inventory_course ic WHERE ic.course_id = CAST(part AS INT)), part)
                                   ELSE part END as varchar(max)) + ' ',
           cast( STUFF(remaining_formula, 1, CHARINDEX(part, remaining_formula) + LEN(part) - 1, '') as varchar(max))
    FROM RecursiveCTE c
         CROSS APPLY (SELECT LEFT(LTRIM(remaining_formula), CHARINDEX(' ', LTRIM(remaining_formula)) - 1) AS part) AS ca
    WHERE LEN(remaining_formula) > 0
)

SELECT formula, updated_formula
FROM RecursiveCTE
WHERE LEN(remaining_formula) = 0;

改进方案与替代实现 #

方案1：优化递归CTE逻辑 #

现有递归存在重复预处理、末尾空格冗余的问题，以下是简化优化后的版本：

WITH RecursiveCTE AS (
    SELECT
        ica.formula AS original_formula,
        REPLACE(REPLACE(ica.formula, ')', ' )'), '(', '( ') AS formula,
        CAST('' AS VARCHAR(MAX)) AS updated_formula,
        CAST(REPLACE(REPLACE(ica.formula, ')', ' )'), '(', '( ') + ' ' AS VARCHAR(MAX)) AS remaining_formula
    FROM courseassociation ica
    UNION ALL
    SELECT
        rc.original_formula,
        rc.formula,
        CAST(rc.updated_formula + 
            CASE
                WHEN ca.part NOT LIKE '%[^0-9]%' THEN COALESCE(ic.course_identifier, ca.part)
                ELSE ca.part
            END + ' ' AS VARCHAR(MAX)),
        CAST(LTRIM(SUBSTRING(rc.remaining_formula, LEN(ca.part) + 1, LEN(rc.remaining_formula))) AS VARCHAR(MAX))
    FROM RecursiveCTE rc
    CROSS APPLY (
        SELECT LEFT(rc.remaining_formula, CHARINDEX(' ', rc.remaining_formula) - 1) AS part
    ) ca
    LEFT JOIN inventory_course ic ON CAST(ca.part AS INT) = ic.course_id
    WHERE LEN(rc.remaining_formula) > 0
)
SELECT
    original_formula AS formula,
    RTRIM(updated_formula) AS converted_formula
FROM RecursiveCTE
WHERE LEN(remaining_formula) = 0;

方案2：非递归数字拆分替换方案 #

避免递归循环，通过生成数字序列定位所有数字片段，批量替换，适合长字符串场景：

WITH Numbers AS (
    -- 生成覆盖最大formula长度的数字序列
    SELECT TOP (SELECT MAX(LEN(formula)) FROM courseassociation) 
        n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
),
FormulaTokens AS (
    SELECT
        ica.formula,
        token = SUBSTRING(ica.formula, n, PATINDEX('%[^0-9]%', SUBSTRING(ica.formula, n, 100) + ' ') - 1),
        start_pos = n,
        token_len = PATINDEX('%[^0-9]%', SUBSTRING(ica.formula, n, 100) + ' ') - 1
    FROM courseassociation ica
    JOIN Numbers n ON n.n <= LEN(ica.formula)
    WHERE SUBSTRING(ica.formula, n, 1) LIKE '[0-9]'
      AND (n = 1 OR SUBSTRING(ica.formula, n-1, 1) NOT LIKE '[0-9]')
),
ReplacedTokens AS (
    SELECT
        ft.formula,
        ft.start_pos,
        ft.token_len,
        replacement = COALESCE(ic.course_identifier, ft.token)
    FROM FormulaTokens ft
    LEFT JOIN inventory_course ic ON CAST(ft.token AS INT) = ic.course_id
)
SELECT
    ica.formula,
    converted_formula = (
        SELECT STRING_AGG(
            CASE 
                WHEN rt.start_pos <= n.n AND n.n <= rt.start_pos + rt.token_len - 1 THEN rt.replacement
                ELSE SUBSTRING(ica.formula, n.n, 1)
            END, '') WITHIN GROUP (ORDER BY n.n)
        FROM Numbers n
        LEFT JOIN ReplacedTokens rt ON n.n BETWEEN rt.start_pos AND rt.start_pos + rt.token_len - 1
        WHERE n.n <= LEN(ica.formula)
    )
FROM courseassociation ica
GROUP BY ica.formula;

方案3：CLR函数（若环境允许） #

如果SQL Server允许启用CLR集成，可编写自定义CLR函数实现字符串替换，性能远高于纯T-SQL方案。需注意配置CLR权限，且符合安全规范。

方案对比 #

• 优化后递归CTE：简化了逻辑，修正了末尾空格问题，保留原实现的可读性
• 非递归拆分方案：避免递归开销，长字符串处理性能更优，无需预处理括号（若不需要括号加空格可移除对应步骤）
• CLR函数：性能最佳，但需要额外环境配置，适合高频处理此类需求的场景

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