如何在SQL中不拆分单词将文本拆分为3个指定长度的列

需求规则 #

需要将文本字段按以下规则拆分为3列，拆分过程中不得截断单词：

• 列1：长度控制在0~50字符
• 列2：长度控制在51~100字符
• 列3：长度控制在101~350字符

示例文本 #

Entering from the hydraulic swim platform aft up the starboard side stairway you enter the aft deck with aft seating, teak dining table, and crew quarters entry hatch to port. Forward to starboard are stairs leading up to the flybridge and to port forward you enter the triple pane sliding glass door to the galley and main salon.

预期输出 #

• Column1 = 'Entering from the hydraulic swim platform aft up'
• Column2 = 'the starboard side stairway you enter the aft deck'
• Column3 = 'with aft seating, teak dining table, and crew quarters entry hatch to port. Forward to starboard are stairs leading up to the flybridge and to port forward you enter the triple pane sliding glass door to the galley and main salon.'

原代码问题分析 #

原有实现的核心bug是处理完每一列后，执行了SET @Counter=@Counter-1操作，导致下一列会重复处理上一列的最后一个单词，因此出现同一个单词同时出现在两列的问题。

修正后可运行代码 #

DECLARE @InputText varchar(350)
SET @InputText=RTRIM(LTRIM(LEFT(
'Entering from the hydraulic swim platform aft up the starboard side stairway you enter the aft deck with aft seating, teak dining table, and crew quarters entry hatch to port. Forward to starboard are stairs leading up to the flybridge and to port forward you enter the triple pane sliding glass door to the galley and main salon.'
,350)))

-- 去除多余的双空格
WHILE CHARINDEX('  ',@InputText)>0 BEGIN 
    SET @InputText=REPLACE(@InputText,'  ',' ');
END

-- 拆分单词存入临时表
DECLARE @T AS TABLE
(
    Id int identity(1,1), 
    ColText varchar(max)
)
INSERT @T 
SELECT value as Text
FROM STRING_SPLIT(@InputText,' ')

---------------------------------------------------------------------
-- 生成列1（最大长度50）
---------------------------------------------------------------------
DECLARE @Col1 varchar(50) = ''
DECLARE @Counter int = 1
DECLARE @InputLength int = 0

WHILE 1=1
BEGIN   
    SET @InputLength = LEN(@Col1) + LEN((SELECT ColText FROM @T WHERE Id=@Counter)) + 1
    IF @InputLength > 50 OR (SELECT ColText FROM @T WHERE Id=@Counter) IS NULL
        BREAK
    SET @Col1 = @Col1 + (SELECT ColText FROM @T WHERE Id=@Counter) + ' '
    SET @Counter = @Counter + 1
END
-- 去掉末尾多余空格
SET @Col1 = RTRIM(@Col1)

---------------------------------------------------------------------
-- 生成列2（最大长度50）
---------------------------------------------------------------------
DECLARE @Col2 varchar(50) = ''
WHILE 1=1
BEGIN   
    SET @InputLength = LEN(@Col2) + LEN((SELECT ColText FROM @T WHERE Id=@Counter)) + 1
    IF @InputLength > 50 OR (SELECT ColText FROM @T WHERE Id=@Counter) IS NULL
        BREAK
    SET @Col2 = @Col2 + (SELECT ColText FROM @T WHERE Id=@Counter) + ' '
    SET @Counter = @Counter + 1
END
SET @Col2 = RTRIM(@Col2)

---------------------------------------------------------------------
-- 生成列3（最大长度250）
---------------------------------------------------------------------
DECLARE @Col3 varchar(250) = ''
WHILE 1=1
BEGIN   
    SET @InputLength = LEN(@Col3) + LEN((SELECT ColText FROM @T WHERE Id=@Counter)) + 1
    IF @InputLength > 250 OR (SELECT ColText FROM @T WHERE Id=@Counter) IS NULL
        BREAK
    SET @Col3 = @Col3 + (SELECT ColText FROM @T WHERE Id=@Counter) + ' '
    SET @Counter = @Counter + 1
END
SET @Col3 = RTRIM(@Col3)

---------------------------------------------------------------------
-- 输出最终结果
---------------------------------------------------------------------
SELECT 
@Col1 AS Col1_Value,
@Col2 AS Col2_Value, 
@Col3 AS Col3_Value

优化方案说明 #

如果使用SQL Server 2022及以上版本，可以用窗口函数计算累加长度，结合STRING_AGG一次性生成三列，代码更简洁易维护，避免循环逻辑出错。

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