如何在SQL中将长文本拆分为符合长度限制的多行分段？

以下实现基于递归CTE完成，可直接适配大多数支持递归语法的数据库（PostgreSQL/MySQL 8.0+/SQL Server等），如果需要调整限制长度，只需修改代码中对应长度参数即可。

基础版：硬截断（不考虑单词完整性） #

直接按指定长度截断文本，无需额外逻辑，性能最优：

WITH RECURSIVE large_texts AS (
  (SELECT 'humongous text goes here' AS text ,'1' AS id)
  UNION ALL 
  (SELECT 'small one' AS text ,'2' AS id)
  UNION ALL
  (SELECT 'and another big one over here' AS text ,'3' AS id)
),
split_texts AS (
  -- 初始递归项：截取前N-1位（N为指定最大长度，示例为10，所以取9位）
  SELECT 
    id,
    LEFT(text, 9) AS text,
    SUBSTRING(text FROM 10) AS remaining_text
  FROM large_texts
  WHERE text != ''
  UNION ALL
  -- 递归拆分剩余文本
  SELECT 
    id,
    LEFT(remaining_text, 9),
    SUBSTRING(remaining_text FROM 10)
  FROM split_texts
  WHERE remaining_text != ''
)
SELECT id, text FROM split_texts ORDER BY id;

执行结果和需求给出的预期输出一致。

进阶版：智能拆分（避免切断单词） #

在截断前查找当前截取范围内最后一个空格的位置，从空格处拆分，保证单词完整性：

WITH RECURSIVE large_texts AS (
  (SELECT 'humongous text goes here' AS text ,'1' AS id)
  UNION ALL 
  (SELECT 'small one' AS text ,'2' AS id)
  UNION ALL
  (SELECT 'and another big one over here' AS text ,'3' AS id)
),
split_texts AS (
  SELECT 
    id,
    CASE 
      -- 前N-1位无空格则直接硬截断
      WHEN STRPOS(REVERSE(LEFT(text, 9)), ' ') = 0 THEN LEFT(text, 9)
      -- 有空格则从最后一个空格处截断，同时去除首尾多余空格
      ELSE TRIM(LEFT(text, 9 - STRPOS(REVERSE(LEFT(text, 9)), ' ') + 1))
    END AS text,
    CASE 
      WHEN STRPOS(REVERSE(LEFT(text, 9)), ' ') = 0 THEN SUBSTRING(text FROM 10)
      ELSE TRIM(SUBSTRING(text FROM 9 - STRPOS(REVERSE(LEFT(text, 9)), ' ') + 2))
    END AS remaining_text
  FROM large_texts
  WHERE text != ''
  UNION ALL
  SELECT 
    id,
    CASE 
      WHEN STRPOS(REVERSE(LEFT(remaining_text, 9)), ' ') = 0 THEN LEFT(remaining_text, 9)
      ELSE TRIM(LEFT(remaining_text, 9 - STRPOS(REVERSE(LEFT(remaining_text, 9)), ' ') + 1))
    END,
    CASE 
      WHEN STRPOS(REVERSE(LEFT(remaining_text, 9)), ' ') = 0 THEN SUBSTRING(remaining_text FROM 10)
      ELSE TRIM(SUBSTRING(remaining_text FROM 9 - STRPOS(REVERSE(LEFT(remaining_text, 9)), ' ') + 2))
    END
  FROM split_texts
  WHERE remaining_text != ''
)
SELECT id, text FROM split_texts ORDER BY id;

不同数据库适配注意事项 #

• MySQL：将STRPOS替换为LOCATE，SUBSTRING(text FROM 10)替换为SUBSTRING(text, 10)即可
• SQL Server：将STRPOS替换为CHARINDEX，递归CTE末尾添加OPTION (MAXRECURSION 0)避免递归次数限制
• 若需要调整最大长度限制为100000字符，只需将代码中所有9替换为99999，10替换为100000即可

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