如何将长字符串插入UTF8排序规则的VARCHAR(N)列？

问题场景 #

数据存在VARCHAR(MAX)列中，需要插入到另一张表的VARCHAR(N)列，排序规则为Czech_100_CI_AS_SC_UTF8。用LEFT、SUBSTRING按字符数截断到目标列长度后，部分数据仍插入失败，报错：

Msg 2628, Level 16, State 1, Line 25
String or binary data would be truncated in table 'tablename', column 'columnname'. Truncated value: 'sometext'.

需要找到一种正确的文本截断方式，既能适配目标列，又能保留尽可能多的内容。

问题原因 #

UTF8排序规则下，VARCHAR列的长度限制是字节数而非字符数：

• 纯拉丁字符（数字、英文字母）占1字节
• 带重音的拉丁字符（比如á）占2字节
• 非拉丁Unicode字符（中文、日语等）占3字节

LEFT、SUBSTRING是按字符数截断，当字符串包含多字节字符时，截断后的总字节数可能超过目标列的字节上限，从而触发截断报错。

复现代码 #

DROP TABLE IF EXISTS [#tmpdl]
GO
CREATE TABLE [#tmpdl] ([Txt] VARCHAR(10) COLLATE Czech_100_CI_AS_SC_UTF8 NULL)
GO
INSERT INTO [#tmpdl] ([Txt]) VALUES (LEFT('123456789abcd', 10)) -- 成功：10个单字节字符，总字节数10
GO
INSERT INTO [#tmpdl] ([Txt]) VALUES (LEFT('123456789ábcd', 10)) -- 失败：前10个字符含1个2字节的á，总字节数=9*1+1*2=11>10
GO
INSERT INTO [#tmpdl] ([Txt]) VALUES (LEFT(N'一二三四', 10)) -- 失败：每个中文占3字节，4个字符占12字节>10
GO

解决方案 #

方法1：自定义函数按字节数逐步截断（兼容所有SQL Server版本） #

通过循环调整截取的字符数，直到总字节数不超过目标列上限：

-- 创建自定义截断函数
CREATE FUNCTION dbo.TruncateUTF8String(@input VARCHAR(MAX), @maxBytes INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX) = @input
    DECLARE @charLen INT = LEN(@result)
    
    WHILE DATALENGTH(@result) > @maxBytes AND @charLen > 0
    BEGIN
        SET @charLen = @charLen - 1
        SET @result = LEFT(@input, @charLen)
    END
    
    RETURN @result
END
GO

-- 使用示例：插入到VARCHAR(10)列
INSERT INTO [#tmpdl] ([Txt]) VALUES (dbo.TruncateUTF8String('123456789ábcd', 10)) -- 截断为'123456789'（9字节）
INSERT INTO [#tmpdl] ([Txt]) VALUES (dbo.TruncateUTF8String(N'一二三四', 10)) -- 截断为'一二三'（9字节）

方法2：用STRING_SPLIT+STRING_AGG逐字符累加（SQL Server 2017+） #

拆分字符串为单个字符，逐个累加直到字节数接近上限：

DECLARE @input VARCHAR(MAX) = '123456789ábcd'
DECLARE @maxBytes INT = 10
DECLARE @result VARCHAR(MAX) = ''
DECLARE @currentBytes INT = 0

SELECT 
    @result = @result + value,
    @currentBytes = @currentBytes + DATALENGTH(value)
FROM STRING_SPLIT(@input, '')
WHERE @currentBytes + DATALENGTH(value) <= @maxBytes

INSERT INTO [#tmpdl] ([Txt]) VALUES (@result)

方法3：CLR函数（需服务器权限） #

如果允许启用CLR集成，可编写.NET函数直接按UTF8字节数截断，效率更高，但需要服务器管理员配置CLR权限。

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