SQL Server REPLACE函数处理超长字符串时出现截断错误问题

问题重现 #

当使用REPLACE()替换长度超过9000字符的模式字符串到10500字符的长文本时，触发字符串截断错误，结果返回NULL。复现代码如下：

DECLARE @LongText NVARCHAR(MAX);
DECLARE @PatternToReplace NVARCHAR(MAX);
DECLARE @Result NVARCHAR(MAX);

-- 生成10500个'X'的字符串
SET @LongText = REPLICATE(N'X', 10500);

-- 生成9000个'X'的替换模式
SET @PatternToReplace = REPLICATE(N'X', 9000);

-- 尝试替换为空格
SET @Result = REPLACE(@LongText, @PatternToReplace, N' ');

-- 输出结果
SELECT 
    LEN(@LongText) AS [OriginalLength],
    LEN(@PatternToReplace) AS [ReplacePatternLength],
    LEN(@Result) AS [ResultLength]

报错信息：

Msg 8152, Level 16, State 10, Line 13
String or binary data would be truncated.

实际输出：

OriginalLength  ReplacePatternLength    ResultLength
-----------------------------------------------------
10500           9000                    NULL

问题原因 #

SQL Server的REPLACE()函数在处理长度超过4000字符的NVARCHAR模式字符串时，会触发隐式类型转换：底层逻辑会将NVARCHAR(MAX)类型的模式字符串强制转换为NVARCHAR(4000)，导致模式字符串被截断，进而在匹配时触发字符串截断错误，最终返回NULL。

解决方案 #

可以通过T-SQL手动实现替换逻辑，避开REPLACE()的隐式转换限制。核心思路是先用CHARINDEX()定位模式字符串的位置，再通过字符串拼接完成替换：

单次匹配替换 #

DECLARE @LongText NVARCHAR(MAX);
DECLARE @PatternToReplace NVARCHAR(MAX);
DECLARE @Result NVARCHAR(MAX);
DECLARE @PatternStart INT;

SET @LongText = REPLICATE(N'X', 10500);
SET @PatternToReplace = REPLICATE(N'X', 9000);

-- 查找模式字符串的起始位置
SET @PatternStart = CHARINDEX(@PatternToReplace, @LongText);

IF @PatternStart > 0
BEGIN
    -- 拼接替换后的字符串：前半部分 + 空格 + 后半部分
    SET @Result = LEFT(@LongText, @PatternStart - 1) 
                  + N' ' 
                  + SUBSTRING(@LongText, @PatternStart + LEN(@PatternToReplace), LEN(@LongText));
END
ELSE
BEGIN
    -- 未找到匹配时返回原字符串
    SET @Result = @LongText;
END

SELECT 
    LEN(@LongText) AS [OriginalLength],
    LEN(@PatternToReplace) AS [ReplacePatternLength],
    LEN(@Result) AS [ResultLength]

执行后将得到预期结果：

OriginalLength  ReplacePatternLength    ResultLength
-----------------------------------------------------
10500           9000                    1500

多次匹配替换 #

如果需要处理文本中多次出现的模式字符串，可以通过循环遍历所有匹配位置，逐步替换：

DECLARE @LongText NVARCHAR(MAX);
DECLARE @PatternToReplace NVARCHAR(MAX);
DECLARE @Result NVARCHAR(MAX);
DECLARE @PatternStart INT;
DECLARE @PatternLen INT;

SET @LongText = REPLICATE(N'X', 10500) + REPLICATE(N'X', 9000); -- 包含两次匹配
SET @PatternToReplace = REPLICATE(N'X', 9000);
SET @Result = @LongText;
SET @PatternLen = LEN(@PatternToReplace);

WHILE 1=1
BEGIN
    SET @PatternStart = CHARINDEX(@PatternToReplace, @Result);
    IF @PatternStart = 0 BREAK;
    
    SET @Result = LEFT(@Result, @PatternStart - 1) 
                  + N' ' 
                  + SUBSTRING(@Result, @PatternStart + @PatternLen, LEN(@Result));
END

SELECT 
    LEN(@LongText) AS [OriginalLength],
    LEN(@PatternToReplace) AS [ReplacePatternLength],
    LEN(@Result) AS [ResultLength]

这个版本会处理所有匹配的模式字符串，最终结果长度为10500+9000 - 9000*2 + 2 = 1502（两次替换各添加一个空格）。

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