Oracle REGEXP_SUBSTR按长度+空格拆分字符串至多列的问题求助

需要将长文本拆分为多个最大长度30字符的片段（作为列），规则为：若第30位是字母，回退至最近空格处拆分，循环直至文本结束。当前SQL执行结果不符合预期，以下是修正方案及分析。

示例预期结果 #

当前实际结果 #

当前使用的SQL代码 #

with spaces as (
 select regexp_instr('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh' || ' '
                    , '[[:space:]]', 1, level) as s
   from dual
connect by level <= regexp_count('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh' || ' '
                                , '[[:space:]]')
        )
, SPLITTED as (
 SELECT MAX(case when s <= 30 then s else 0 end) as a
      , max(case when s <= 60 then s else 0 end) as b
      , max(case when s <= 90 then s else 0 end) as c
      , max(case when s <= 120 then s else 0 end) as d
   from spaces
        )
select Trim(substr('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh',1,a)) AS FIRST30
     , Trim(substr('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh',a, b - a))  AS SECOND30
     , Trim(substr('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh',b, c - b)) AS THIRD30
     , Trim(substr('For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh',c, d - c)) AS FOURTH30
  FROM SPLITTED

问题分析 #

原SQL的核心缺陷是按固定的30、60、90等倍数位置找空格，而非基于上一段的实际结束位置计算下一段的范围。比如第二段本应从第一段结束位置（假设为X）开始，取X到X+29范围内的最后空格，但原逻辑直接找<=60的最大空格，导致第二段长度超过30限制。

修正方案：递归CTE动态拆分 #

使用递归CTE可以实现迭代式拆分，每一段都基于上一段的结束位置计算，严格遵守“最大30字符且不拆单词”的规则：

WITH RECURSIVE text_split AS (
    -- 初始化第一段：从文本开头开始
    SELECT 
        1 AS segment_num,
        1 AS start_pos,
        -- 确定第一段的结束位置：前30字符内的最后一个空格
        CASE 
            WHEN SUBSTR(long_text, 30, 1) NOT LIKE '[[:space:]]' 
            THEN REGEXP_INSTR(long_text, '[[:space:]]', 1, 1, 0, 'n', 1, 30)
            ELSE 30
        END AS end_pos,
        long_text
    FROM (
        SELECT 'For Heaven only knows why anyone loves it so, how one sees it so, making it up, building it round one, tumbling it, creating it every moment afresh' AS long_text
        FROM dual
    ) t
    UNION ALL
    -- 递归处理后续段落
    SELECT 
        segment_num + 1,
        end_pos + 1,
        CASE 
            -- 处理文本剩余长度不足30的情况
            WHEN end_pos + 30 > LENGTH(long_text)
            THEN LENGTH(long_text)
            -- 若当前段第30位不是空格，反向找最近空格
            WHEN SUBSTR(long_text, end_pos + 30, 1) NOT LIKE '[[:space:]]'
            THEN end_pos + REGEXP_INSTR(SUBSTR(long_text, end_pos + 1, 30), '[[:space:]]', 1, 1, 0, 'n')
            ELSE end_pos + 30
        END AS end_pos,
        long_text
    FROM text_split
    WHERE end_pos < LENGTH(long_text)
)
-- 转换为需求的列格式
SELECT
    TRIM(SUBSTR(long_text, start_pos, end_pos - start_pos + 1)) AS Value,
    CASE segment_num
        WHEN 1 THEN 'FIRST30'
        WHEN 2 THEN 'SECOND30'
        WHEN 3 THEN 'THIRD30'
        WHEN 4 THEN 'FOURTH30'
        ELSE 'OTHER' || segment_num || '30'
    END AS Column_Name
FROM text_split
ORDER BY segment_num;

方案优势 #

1. 动态适配：自动处理文本长度变化，无需预先设定拆分段数；
2. 严格遵守规则：每一段都确保不超过30字符且不拆分单词；
3. 可扩展性：如果需要拆分成更多段，无需修改核心逻辑，递归会自动处理。

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