Oracle构建REGEXP_SUBSTR模式提取括号内内容并拼接

核心思路 #

通过REGEXP_SUBSTR捕获括号内的目标内容，结合CONNECT BY生成对应数量的拆分行，最后用LISTAGG将多行结果拼接为逗号分隔的字符串。

正则表达式说明 #

使用模式\[([^\]]+)\]精准匹配括号内容：

• \[ 和 \]：转义方括号，匹配实际的[和]字符
• ([^\]]+)：捕获组，匹配除]之外的任意字符（即括号包裹的核心内容）

完整SQL示例 #

-- 模拟测试数据，替换为你的实际表和列名即可
WITH test_data AS (
    SELECT 'blah blah blah [this text 1] yada yada [that text 2] da-da-da' AS content FROM dual
    UNION ALL
    SELECT 'no brackets in this record' FROM dual
    UNION ALL
    SELECT '[first item] [second item with spaces] [third]' FROM dual
)
SELECT
    content,
    -- 用NVL处理无括号的情况，返回空字符串而非NULL
    NVL(LISTAGG(REGEXP_SUBSTR(content, '\[([^\]]+)\]', 1, level, NULL, 1), ', ') WITHIN GROUP (ORDER BY level), '') AS extracted_content
FROM test_data
CONNECT BY
    -- 控制层级数等于当前记录的括号内容数量
    level <= REGEXP_COUNT(content, '\[([^\]]+)\]')
    -- 保证同一条记录内生成层级，避免跨记录产生笛卡尔积
    AND PRIOR content = content
    AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY content;

关键参数解释 #

1. REGEXP_SUBSTR(content, '\[([^\]]+)\]', 1, level, NULL, 1)
   • 第5个参数NULL：使用默认匹配规则
   • 第6个参数1：返回第一个捕获组的内容（仅括号内的文本，不含[]）
2. REGEXP_COUNT(content, '\[([^\]]+)\]')：统计当前记录中括号内容的数量，作为层级循环的上限
3. PRIOR SYS_GUID() IS NOT NULL：防止CONNECT BY出现循环，确保每条记录独立生成拆分行

输出效果 #

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