Oracle SQL实现长字符串按最多60字符拆分为4段且不拆分单词的最优方法

Got it, let's tackle that frustrating word-splitting issue in your transcript text query. The core problem with using plain substr() is that it cuts text at fixed character positions, regardless of word boundaries—so here's an optimized, Oracle-native solution that splits your aggregated text into up to 4 segments, each under 60 characters, ending with a space, and never breaking a word.

The Approach #

Instead of brute-force character slicing, we'll use Oracle's regex functions to:

1. First aggregate all transcript text for each employee/sequence (to avoid re-computing LISTAGG multiple times)
2. For each segment, find the last space within the 60-character limit to split cleanly between words
3. Ensure every segment ends with a space, even if the remaining text is shorter than 60 characters

Full SQL Query #

WITH aggregated_transcripts AS (
    SELECT 
        emplid AS SHRQPNM_PIDM,
        print_loc_SEQ,
        LISTAGG(SSR_transcript_txt) WITHIN GROUP (ORDER BY text_seq_nbr) AS full_text,
        TO_CHAR(SYSDATE, 'YYYYMMDD') AS SHRQPNM_ACTIVITY_D
    FROM PS_TSCRPT_TEXT
    GROUP BY emplid, print_loc_SEQ
)
SELECT 
    SHRQPNM_PIDM,
    -- Segment 1: Grab first up-to-60 chars ending with a space
    REGEXP_SUBSTR(full_text, '^.{1,60}\s', 1, 1, 'n') AS segment_1,
    -- Segment 2: Start right after segment 1 ends, repeat the pattern
    REGEXP_SUBSTR(
        full_text, 
        '.{1,60}\s', 
        REGEXP_INSTR(full_text, '^.{1,60}\s') + 1, 
        1, 
        'n'
    ) AS segment_2,
    -- Segment 3: Start right after segment 2 ends
    REGEXP_SUBSTR(
        full_text, 
        '.{1,60}\s', 
        REGEXP_INSTR(
            full_text, 
            '.{1,60}\s', 
            REGEXP_INSTR(full_text, '^.{1,60}\s') + 1
        ) + 1, 
        1, 
        'n'
    ) AS segment_3,
    -- Segment 4: Handle remaining text, ensure it ends with a space
    CASE 
        WHEN REGEXP_SUBSTR(
            full_text, 
            '.+', 
            REGEXP_INSTR(
                full_text, 
                '.{1,60}\s', 
                REGEXP_INSTR(
                    full_text, 
                    '.{1,60}\s', 
                    REGEXP_INSTR(full_text, '^.{1,60}\s') + 1
                ) + 1
            ) + 1
        ) IS NOT NULL
        THEN RTRIM(REGEXP_SUBSTR(
            full_text, 
            '.+', 
            REGEXP_INSTR(
                full_text, 
                '.{1,60}\s', 
                REGEXP_INSTR(
                    full_text, 
                    '.{1,60}\s', 
                    REGEXP_INSTR(full_text, '^.{1,60}\s') + 1
                ) + 1
            ) + 1
        )) || ' '
        ELSE NULL
    END AS segment_4,
    SHRQPNM_ACTIVITY_D
FROM aggregated_transcripts
ORDER BY SHRQPNM_PIDM, print_loc_SEQ;

Breakdown of Key Parts #

• CTE aggregated_transcripts: We first compute the full aggregated text once per group, which is more efficient than calling LISTAGG four times in the main query.
• Regex for Segment 1: REGEXP_SUBSTR(full_text, '^.{1,60}\s', 1, 1, 'n') matches from the start of the text, up to 60 characters, and stops at the last space before that limit—guaranteeing no broken words and a trailing space.
• Subsequent Segments: We use REGEXP_INSTR to find the end position of the previous segment, then start the next segment from that position + 1, repeating the same word-safe pattern.
• Segment 4 Handling: For the final segment, if there's remaining text, we trim any accidental trailing spaces and append a single space to meet your requirement of every segment ending with a space.

Edge Cases Covered #

• Text shorter than 60 characters: The first segment will capture the full text (with a trailing space), and the rest will be NULL.
• Exact 60-character segments ending in a space: The regex will match cleanly without extra trimming.
• Remaining text in segment 4 shorter than 60: We ensure it still ends with a space as required.

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