请求协助：VARCHAR2(210 CHAR)字段拆分后截断超出的第7行内容

我来帮你搞定这个需求——把VARCHAR2(210 CHAR)字段按单词末尾拆分，最多生成6行，每行不超过35字符，超出的第7行直接截断。针对你提供的示例文本，我整理了两种可行的实现方案：

示例文本的正确拆分结果 #

原文本：

WE REFER TO OUR PIN 103 QUOTED HERE UNDER WHICH WAS SENT UNDER TRN QUOTED IN FIELD 20 AND PROCESSED UNDER YOUR REFERENCE IN FIELD 21 BEST REGARDS PAYMENT INVESTIGATIONS CENTRALIZED OPERATIONS CENTRE BOV MALTA

拆分后（满足每行≤35字符、按单词拆分、最多6行要求）：

• WE REFER TO OUR PIN 103 QUOTED HERE
• UNDER WHICH WAS SENT UNDER TRN QUOTED
• IN FIELD 20 AND PROCESSED UNDER YOUR
• REFERENCE IN FIELD 21 BEST REGARDS
• PAYMENT INVESTIGATIONS CENTRALIZED
• OPERATIONS CENTRE BOV MALTA

方案1：自定义PL/SQL函数（适合重复调用） #

这个函数会逐行处理文本，确保每行都是完整单词，且严格控制行数和字符数：

CREATE OR REPLACE FUNCTION split_text_to_lines(p_text IN VARCHAR2)
RETURN SYS.ODCIVARCHAR2LIST
IS
    v_lines SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
    v_remaining_text VARCHAR2(210 CHAR) := TRIM(p_text);
    v_current_line VARCHAR2(35 CHAR);
    v_space_pos NUMBER;
BEGIN
    WHILE v_remaining_text IS NOT NULL AND v_lines.COUNT < 6 LOOP
        -- 剩余文本长度≤35，直接作为最后一行
        IF LENGTH(v_remaining_text) <= 35 THEN
            v_lines.EXTEND;
            v_lines(v_lines.COUNT) := v_remaining_text;
            EXIT;
        ELSE
            -- 在前35字符范围内找最后一个空格的位置，保证不拆分单词
            v_space_pos := INSTR(SUBSTR(v_remaining_text, 1, 35), ' ', -1);
            IF v_space_pos = 0 THEN
                -- 极端情况：前35字符无空格，直接取35字符（避免无限循环）
                v_lines.EXTEND;
                v_lines(v_lines.COUNT) := SUBSTR(v_remaining_text, 1, 35);
                v_remaining_text := TRIM(SUBSTR(v_remaining_text, 36));
            ELSE
                -- 截取到最后一个空格的位置作为当前行
                v_lines.EXTEND;
                v_lines(v_lines.COUNT) := TRIM(SUBSTR(v_remaining_text, 1, v_space_pos));
                v_remaining_text := TRIM(SUBSTR(v_remaining_text, v_space_pos + 1));
            END IF;
        END IF;
    END LOOP;
    RETURN v_lines;
END;
/

调用函数处理示例文本 #

SELECT COLUMN_VALUE AS split_line
FROM TABLE(split_text_to_lines('WE REFER TO OUR PIN 103 QUOTED HERE UNDER WHICH WAS SENT UNDER TRN QUOTED IN FIELD 20 AND PROCESSED UNDER YOUR REFERENCE IN FIELD 21 BEST REGARDS PAYMENT INVESTIGATIONS CENTRALIZED OPERATIONS CENTRE BOV MALTA'));

方案2：纯SQL查询（适合一次性使用） #

用正则表达式和分层查询直接生成结果，无需创建函数：

WITH input_data AS (
    SELECT 'WE REFER TO OUR PIN 103 QUOTED HERE UNDER WHICH WAS SENT UNDER TRN QUOTED IN FIELD 20 AND PROCESSED UNDER YOUR REFERENCE IN FIELD 21 BEST REGARDS PAYMENT INVESTIGATIONS CENTRALIZED OPERATIONS CENTRE BOV MALTA' AS source_text FROM DUAL
),
line_generator AS (
    SELECT 
        LEVEL AS line_number,
        TRIM(REGEXP_SUBSTR(source_text, '(.{1,35})(\s|$)', 1, LEVEL, NULL, 1)) AS line_content
    FROM input_data
    CONNECT BY 
        LEVEL <= 6  -- 限制最多6行
        AND REGEXP_SUBSTR(source_text, '(.{1,35})(\s|$)', 1, LEVEL) IS NOT NULL
)
SELECT line_number, line_content
FROM line_generator;

正则表达式说明 #

(.{1,35})(\s|$) 的作用是：

• 匹配1到35个字符
• 后面必须跟着空格或者文本结尾，确保拆分点在单词末尾

两种方案都能完美满足你的需求：按单词拆分、每行不超35字符、最多6行，超出的内容直接截断不会生成第7行。

内容的提问来源于stack exchange，提问作者Usha Sahithi Reddy