使用REGEXP_SUBSTR拆分SQL多分隔符字符串行转列失败及扩展需求

问题场景 #

原messages表结构及数据：

CREATE TABLE messages (
    username VARCHAR2(50),
    id_criteria VARCHAR2(100),
    code_criteria VARCHAR2(100)
);

INSERT INTO messages VALUES ('Kim', '22:', '21-23:21-22');
INSERT INTO messages VALUES ('Tim', '36:', 'All');
INSERT INTO messages VALUES ('Sam', '21,22,23:24:25:', NULL);

原SQL尝试用REGEXP_SUBSTR+CONNECT BY拆分id_criteria，但仅返回Sam的部分数据，且未处理code_criteria的拆分需求。

原SQL的问题 #

1. 正则表达式逻辑错误：[^:|^,]+写法错误，^在方括号内是取反的起始符号，此处应使用[^:,]+匹配冒号/逗号分隔的非空片段。
2. CONNECT BY缺少防循环条件：未加PRIOR username = username和PRIOR SYS_GUID() IS NOT NULL，导致递归时出现笛卡尔积或循环，丢失部分用户的数据。

修正后的id_criteria拆分SQL #

先解决单列拆分问题，确保所有用户的完整数据返回：

SELECT 
    username,
    TRIM(REGEXP_SUBSTR(id_criteria, '[^:,]+', 1, LEVEL)) AS idrefval
FROM messages
CONNECT BY 
    LEVEL <= REGEXP_COUNT(id_criteria, '[^:,]+')
    AND PRIOR username = username
    AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY username, LEVEL;

关键说明 #

• REGEXP_COUNT(id_criteria, '[^:,]+')：计算当前行可拆分的片段数量，限制层级数避免无效循环。
• PRIOR username = username：确保每个用户的拆分独立进行，不会跨用户产生关联。
• PRIOR SYS_GUID() IS NOT NULL：生成唯一随机值打破递归循环，防止Oracle误判父行关系。

扩展到code_criteria的拆分 #

需要处理code_criteria的三种边界情况：NULL、All、冒号分隔的范围值，最终实现双列同时拆分：

SELECT 
    m.username,
    TRIM(REGEXP_SUBSTR(m.id_criteria, '[^:,]+', 1, l_id.id_level)) AS idrefval,
    CASE 
        WHEN m.code_criteria IS NULL THEN NULL
        WHEN m.code_criteria = 'All' THEN 'All'
        ELSE TRIM(REGEXP_SUBSTR(m.code_criteria, '[^:]+', 1, l_code.code_level))
    END AS coderefval
FROM messages m
-- 生成id_criteria的最大拆分层级
CROSS JOIN (
    SELECT LEVEL AS id_level 
    FROM dual 
    CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(id_criteria, '[^:,]+')) FROM messages)
) l_id
-- 生成code_criteria的最大拆分层级（排除NULL和All的情况）
LEFT JOIN (
    SELECT LEVEL AS code_level 
    FROM dual 
    CONNECT BY LEVEL <= (
        SELECT NVL(MAX(REGEXP_COUNT(code_criteria, '[^:]+')), 1) 
        FROM messages 
        WHERE code_criteria IS NOT NULL AND code_criteria != 'All'
    )
) l_code 
    ON (m.code_criteria IS NULL OR m.code_criteria = 'All') AND l_code.code_level = 1
    OR (m.code_criteria IS NOT NULL AND m.code_criteria != 'All' 
        AND l_code.code_level <= REGEXP_COUNT(m.code_criteria, '[^:]+'))
-- 过滤id_criteria拆分后的空片段（原数据末尾冒号产生的无效值）
WHERE TRIM(REGEXP_SUBSTR(m.id_criteria, '[^:,]+', 1, l_id.id_level)) IS NOT NULL
ORDER BY m.username, l_id.id_level, l_code.code_level;

预期结果 #

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