如何修复SQL多分隔符替换时查询返回多行的问题？

你的问题根源很明确：当前的LEFT JOIN会为每个匹配的base64ID生成一行记录，每行只替换一个<##ID##>占位符，所以有多少个占位符就会返回多少行。要实现单一行内完成所有替换，可以用以下几种方案，根据你的数据库版本和权限选择：

方案1：递归CTE（无需自定义函数，SQL Server适用） #

递归CTE会逐次替换每个占位符，最后取每个tableA行的最终替换结果：

WITH RecursiveReplace AS (
    -- 初始步骤：获取原始文本和所有待替换的占位符/内容对
    SELECT 
        A.ID AS tableA_ID,
        A.notesColumn AS updatedNote,
        '<##' + CAST(B.base64ID AS VARCHAR(25)) + '##>' AS placeholder,
        B.docImage AS replacement,
        ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.base64ID) AS rn
    FROM tableA A
    LEFT JOIN base64Table B ON A.ID = B.tableANote
    WHERE A.pageID = @pageID

    UNION ALL

    -- 递归替换：每次替换一个占位符，直到所有替换完成
    SELECT 
        r.tableA_ID,
        REPLACE(r.updatedNote, r.placeholder, r.replacement) AS updatedNote,
        '<##' + CAST(B.base64ID AS VARCHAR(25)) + '##>' AS placeholder,
        B.docImage AS replacement,
        r.rn + 1 AS rn
    FROM RecursiveReplace r
    JOIN base64Table B ON r.tableA_ID = B.tableANote
    -- 确保每次替换下一个未处理的占位符
    WHERE B.base64ID > SUBSTRING(r.placeholder, 4, LEN(r.placeholder)-7)
)
-- 提取每个tableA行的最终替换结果（最后一次递归的行）
SELECT 
    updatedNote AS noteText
FROM (
    SELECT 
        tableA_ID,
        updatedNote,
        ROW_NUMBER() OVER (PARTITION BY tableA_ID ORDER BY rn DESC) AS finalRn
    FROM RecursiveReplace
) t
WHERE finalRn = 1;

方案2：自定义标量函数（直观易维护，适合有函数创建权限的场景） #

创建一个函数来遍历所有待替换项，在单一行内完成所有替换：

CREATE FUNCTION dbo.ReplaceAllPlaceholders(@originalText VARCHAR(MAX), @tableAID INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @updatedText VARCHAR(MAX) = @originalText;
    DECLARE @placeholder VARCHAR(MAX), @replacement VARCHAR(MAX);

    -- 游标遍历当前tableA行对应的所有替换规则
    DECLARE replacementCursor CURSOR FOR
        SELECT '<##' + CAST(base64ID AS VARCHAR(25)) + '##>', docImage
        FROM base64Table
        WHERE tableANote = @tableAID;

    OPEN replacementCursor;
    FETCH NEXT FROM replacementCursor INTO @placeholder, @replacement;

    -- 逐个替换占位符
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @updatedText = REPLACE(@updatedText, @placeholder, @replacement);
        FETCH NEXT FROM replacementCursor INTO @placeholder, @replacement;
    END

    CLOSE replacementCursor;
    DEALLOCATE replacementCursor;

    RETURN @updatedText;
END

然后调用函数查询：

SELECT dbo.ReplaceAllPlaceholders(A.notesColumn, A.ID) AS noteText
FROM tableA A
WHERE A.pageID = @pageID;

方案3：XML聚合替换（SQL Server 2017+适用） #

利用FOR XML PATH将替换操作嵌套，一次性完成所有替换：

SELECT 
    -- 通过XML聚合生成嵌套的REPLACE语句，逐次替换所有占位符
    (SELECT REPLACE(x.value('.', 'VARCHAR(MAX)'), r.placeholder, r.replacement)
     FROM (VALUES(A.notesColumn)) AS t(x)
     CROSS APPLY (
         SELECT 
             '<##' + CAST(base64ID AS VARCHAR(25)) + '##>' AS placeholder,
             docImage AS replacement
         FROM base64Table
         WHERE tableANote = A.ID
     ) AS r
     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS noteText
FROM tableA A
WHERE A.pageID = @pageID;

关键思路总结 #

所有方案的核心都是避免将每个替换项拆分为单独的行，而是在单一行的上下文内完成所有占位符的替换操作。根据你的数据库版本、权限和性能需求选择最适合的方式即可。

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