T-SQL如何同时替换字符串中两个值并避免重复编写替换逻辑

下面提供两种可直接落地的优化方案，都可以彻底消除重复的替换逻辑：

方案1：封装替换逻辑为自定义函数（推荐，可复用性最高） #

首先创建一个通用的变量替换函数，后续只要传入要处理的字符串、变量名、替换值就可以完成替换：

-- 创建通用替换函数，入参：原字符串、待替换变量名（比如$tablelist）、替换后的值
CREATE FUNCTION dbo.ReplaceVariable(@OriginalStr NVARCHAR(MAX), @VarName NVARCHAR(100), @ReplaceValue NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ReplaceStart INT, @ReplaceLength INT
    SET @ReplaceStart = PATINDEX('%' + @VarName + '=''%', @OriginalStr) + LEN(@VarName + '=''')
    SET @ReplaceLength = PATINDEX('%''%', SUBSTRING(@OriginalStr, @ReplaceStart, LEN(@OriginalStr)))
    RETURN REPLACE(@OriginalStr, SUBSTRING(@OriginalStr, @ReplaceStart, @ReplaceLength - 1), @ReplaceValue)
END
GO

业务代码可以简化为：

SET @originalCommand = '$tablelist=''table_1'', $dms=''dms_1'''

PRINT '*** Original command text:';
PRINT @originalCommand;

-- 处理tablelist的替换值
IF @tableList = ''
    SET @tableReplaceValue = CONCAT(@schemaName, '.', @tableName)
ELSE
    SET @tableReplaceValue = @tableList;

-- 两次调用函数完成替换即可
SET @newCommand = dbo.ReplaceVariable(@originalCommand, '$tablelist', @tableReplaceValue)
SET @newCommand = dbo.ReplaceVariable(@newCommand, '$dms', @dms)

PRINT '';
PRINT '*** New command text:';
PRINT @newCommand;

后续如果要加新的变量替换，只要新增一行调用函数的代码即可，不用再重复写查找、计算长度的逻辑。

方案2：用表变量批量循环替换（无需新建函数，适合临时脚本场景） #

如果你的场景不允许新建数据库函数，可以用表变量存所有要替换的变量和对应值，循环遍历完成替换：

SET @originalCommand = '$tablelist=''table_1'', $dms=''dms_1'''

PRINT '*** Original command text:';
PRINT @originalCommand;

-- 先计算好所有变量的替换值
IF @tableList = ''
    SET @tableReplaceValue = CONCAT(@schemaName, '.', @tableName)
ELSE
    SET @tableReplaceValue = @tableList;

-- 定义表变量存所有要替换的变量和对应值，后续加变量只要在这里加一行
DECLARE @ReplaceMap TABLE (VarName NVARCHAR(100), ReplaceValue NVARCHAR(MAX))
INSERT INTO @ReplaceMap VALUES 
('$tablelist', @tableReplaceValue),
('$dms', @dms)

DECLARE @CurrentVar NVARCHAR(100), @CurrentValue NVARCHAR(MAX), @ReplaceStart INT, @ReplaceLength INT
SET @newCommand = @originalCommand

-- 循环遍历完成所有替换
DECLARE replace_cursor CURSOR FOR SELECT VarName, ReplaceValue FROM @ReplaceMap
OPEN replace_cursor
FETCH NEXT FROM replace_cursor INTO @CurrentVar, @CurrentValue
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ReplaceStart = PATINDEX('%' + @CurrentVar + '=''%', @newCommand) + LEN(@CurrentVar + '=''')
    SET @ReplaceLength = PATINDEX('%''%', SUBSTRING(@newCommand, @ReplaceStart, LEN(@newCommand)))
    SET @newCommand = REPLACE(@newCommand, SUBSTRING(@newCommand, @ReplaceStart, @ReplaceLength - 1), @CurrentValue)
    FETCH NEXT FROM replace_cursor INTO @CurrentVar, @CurrentValue
END
CLOSE replace_cursor
DEALLOCATE replace_cursor

PRINT '';
PRINT '*** New command text:';
PRINT @newCommand;

两种方案的运行结果和原有代码完全一致，同时彻底消除了重复的替换逻辑，后续新增变量替换的成本极低。

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