Oracle中按分号拆分字符串为多行的通用SQL查询方法

假设你的表名为your_table，以下几种方法可以实现将SENTENCE字段按分号拆分为单独行，同时保留对应ID：

方法1：分层查询（兼容Oracle 11g及更早版本） #

这是适配大多数Oracle版本的通用方案：

SELECT
    t.ID,
    TRIM(REGEXP_SUBSTR(t.SENTENCE, '[^;]+', 1, LEVEL)) AS SENTENCE
FROM
    your_table t
CONNECT BY
    LEVEL <= REGEXP_COUNT(t.SENTENCE, ';') + 1
    AND PRIOR t.ID = t.ID
    AND PRIOR SYS_GUID() IS NOT NULL;

• REGEXP_COUNT统计SENTENCE中分号的数量，确定需要拆分的行数
• REGEXP_SUBSTR按层级截取每个分号分隔的子串
• TRIM清理子串前后可能存在的空格
• PRIOR SYS_GUID() IS NOT NULL避免同一ID下的行产生循环关联

方法2：递归CTE（Oracle 11g R2+） #

适合需要自定义拆分逻辑的场景：

WITH split_data AS (
    SELECT
        ID,
        SENTENCE,
        1 AS pos,
        REGEXP_INSTR(SENTENCE, ';', 1, 1) AS next_pos
    FROM your_table
    UNION ALL
    SELECT
        ID,
        SENTENCE,
        pos + 1,
        REGEXP_INSTR(SENTENCE, ';', 1, pos + 1)
    FROM split_data
    WHERE next_pos > 0
)
SELECT
    ID,
    TRIM(CASE
        WHEN next_pos > 0 THEN SUBSTR(SENTENCE, pos, next_pos - pos)
        ELSE SUBSTR(SENTENCE, pos)
    END) AS SENTENCE
FROM split_data
ORDER BY ID, pos;

通过递归逐步定位每个分号的位置，截取对应的子串，最后按ID和拆分顺序排序。

方法3：JSON_TABLE（Oracle 12c+） #

利用Oracle 12c引入的JSON处理能力，代码更简洁：

SELECT
    t.ID,
    TRIM(j.sentence) AS SENTENCE
FROM
    your_table t,
    JSON_TABLE(
        '["' || REPLACE(t.SENTENCE, ';', '","') || '"]',
        '$[*]' COLUMNS sentence VARCHAR2(4000) PATH '$'
    ) j;

先将分号分隔的字符串转换为JSON数组，再通过JSON_TABLE将数组元素拆分为单独行。

以上方法均能处理SENTENCE字段中不定数量的分号分隔内容，输出结果与你期望的一致。

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