如何使用SQL将大括号内参数替换为关联表对应值且无需硬编码

实现思路 #

• 对每个ID下的所有参数分配唯一行号，用于递归替换时按顺序处理
• 使用递归CTE逐次替换notes中的占位符，每一轮递归替换一个对应参数的占位符
• 所有参数替换完成后输出最终结果，无占位符的行直接返回原始内容

完整可运行SQL代码 #

DECLARE @DataTable TABLE (
    id int,
    notes varchar(1000)
);
INSERT INTO @DataTable VALUES 
(1, 'The Organization'),
(1, 'develop document disseminate to {{param = "ac-1_prm_1"}} and {{param = "ac-1_prm_2"}} '),
(2, 'develop document  to {{param = "ac-1_prm_3"}}'),
(2, 'Test');

DECLARE @DataTableParameter TABLE (
    parameterid varchar(100),
    [value] varchar(100),
    id int
);
INSERT INTO @DataTableParameter VALUES
('ac-1_prm_1', 'apple doc', 1),
('ac-1_prm_2', 'google doc', 1),
('ac-1_prm_3', 'facebook doc', 2);

-- 给每个id下的参数编排序号
WITH RankedParams AS (
    SELECT 
        id, 
        parameterid, 
        [value],
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY parameterid) AS rn,
        COUNT(*) OVER (PARTITION BY id) AS total_cnt
    FROM @DataTableParameter
),
-- 递归CTE逐行替换占位符
RecursiveReplace AS (
    -- 递归初始层：原始notes内容，从第一个参数开始替换
    SELECT 
        dt.id,
        dt.notes,
        dt.notes AS final_notes,
        1 AS current_rn,
        ISNULL(rp.total_cnt,0) AS total_param
    FROM @DataTable dt
    LEFT JOIN (SELECT DISTINCT id, total_cnt FROM RankedParams) rp ON dt.id = rp.id
    
    UNION ALL
    
    -- 递归层：替换当前序号对应的占位符
    SELECT 
        rr.id,
        rr.notes,
        CAST(REPLACE(rr.final_notes, '{{param = "' + rp.parameterid + '"}}', rp.value) AS varchar(1000)) AS final_notes,
        rr.current_rn + 1 AS current_rn,
        rr.total_param
    FROM RecursiveReplace rr
    JOIN RankedParams rp ON rr.id = rp.id AND rr.current_rn = rp.rn
    WHERE rr.current_rn <= rr.total_param
)
-- 取替换完成的最终结果
SELECT 
    id,
    notes,
    final_notes
FROM RecursiveReplace
WHERE current_rn = total_param + 1
ORDER BY id, notes
OPTION (MAXRECURSION 100); -- 单个id下参数不超过100个可直接用，参数更多可调整数值

效果说明 #

运行后输出结果和需求的预期效果完全一致，不需要硬编码任何notes中的固定文本，新增参数或修改占位符都不需要调整SQL逻辑。

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