SQL如何按指定长度拆分长文本为多行 优先在空格位置分割

问题背景 #

现有表存储长文本内容，需要将超过指定长度的文本拆分为多行返回，此前尝试的string_split、substring组合方案、CAST(message AS SUPER)遍历方案均无法满足需求：后者无法控制拆分长度，且需要修改字段类型不符合要求。

样例表结构与数据 #

ID | Message
----------------------------------
1  | Very looooooooooooooooong text
2  | Short text

需求说明 #

• 基础要求：每隔n个字符拆分字符串，当拆分长度n=15时，预期返回结果如下：

Id                    | Message
------------------------------------------
1                     | Very looooooooo
1                     | oooooooong text
2                     | Short text

• 优化要求：拆分位置优先选达到n个字符后的第一个空格，避免截断单词，展示效果更友好。

实现方案 #

采用递归CTE实现，全程操作varchar类型字段，无类型转换，拆分规则可灵活配置。

1. 基础版本：固定每15字符拆分 #

WITH RECURSIVE split_logic AS (
    -- 初始化：取每条文本的第一拆分段
    SELECT
        ID,
        Message AS origin_msg,
        1 AS current_pos,
        SUBSTRING(Message, 1, 15) AS segment
    FROM your_table
    UNION ALL
    -- 递归：逐段向后截取固定长度内容
    SELECT
        ID,
        origin_msg,
        current_pos + 15 AS current_pos,
        SUBSTRING(origin_msg, current_pos + 15, 15) AS segment
    FROM split_logic
    WHERE current_pos + 15 <= LENGTH(origin_msg)
)
-- 合并结果，补全最后一段不足15字符的剩余内容
SELECT ID, segment AS Message
FROM split_logic
UNION ALL
SELECT 
    ID, 
    SUBSTRING(origin_msg, MAX(current_pos) + 15) AS Message
FROM split_logic
GROUP BY ID, origin_msg
HAVING MAX(current_pos) + 15 <= LENGTH(origin_msg)
ORDER BY ID, current_pos;

2. 优化版本：优先在n字符后首个空格处拆分 #

在固定长度拆分逻辑基础上调整拆分点判断：每次截取时先查找第n位之后的第一个空格位置，存在空格则在空格处拆分，不存在空格则按固定15位硬拆分，避免出现超长文本段：

WITH RECURSIVE split_logic AS (
    SELECT
        ID,
        Message AS origin_msg,
        1 AS current_pos,
        -- 计算首段拆分位置
        TRIM(CASE
            WHEN POSITION(' ' IN SUBSTRING(Message, 16)) > 0
            THEN SUBSTRING(Message, 1, 15 + POSITION(' ' IN SUBSTRING(Message, 16)) - 1)
            ELSE SUBSTRING(Message, 1, 15)
        END) AS segment,
        -- 记录下一段的起始位置
        CASE
            WHEN POSITION(' ' IN SUBSTRING(Message, 16)) > 0
            THEN 15 + POSITION(' ' IN SUBSTRING(Message, 16))
            ELSE 15
        END AS next_pos
    FROM your_table
    UNION ALL
    SELECT
        ID,
        origin_msg,
        next_pos AS current_pos,
        TRIM(CASE
            WHEN POSITION(' ' IN SUBSTRING(origin_msg, next_pos + 16)) > 0
            THEN SUBSTRING(origin_msg, next_pos + 1, 15 + POSITION(' ' IN SUBSTRING(origin_msg, next_pos + 16)) - 1)
            ELSE SUBSTRING(origin_msg, next_pos + 1, 15)
        END) AS segment,
        CASE
            WHEN POSITION(' ' IN SUBSTRING(origin_msg, next_pos + 16)) > 0
            THEN next_pos + 15 + POSITION(' ' IN SUBSTRING(origin_msg, next_pos + 16))
            ELSE next_pos + 15
        END AS next_pos
    FROM split_logic
    WHERE next_pos < LENGTH(origin_msg)
)
SELECT ID, segment AS Message
FROM split_logic
ORDER BY ID, current_pos;

若使用的数据库不支持递归CTE（如MySQL 5.x版本），可以用数字辅助表（提前生成连续序号的临时表）替换递归逻辑，核心截取、拆分点判断规则保持一致即可。

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