Oracle SQL如何使用正则表达式提取字段中所有匹配子串

Oracle 11g 及以上通用方案 #

这是兼容性最好的实现方式，支持返回拼接后的结果列表，也支持返回每行一个匹配项的格式。

合并为单个逗号分隔字符串的写法 #

SELECT LISTAGG(REGEXP_SUBSTR(t.text, 'obj[^,]*', 1, LEVEL), ', ') 
       WITHIN GROUP (ORDER BY LEVEL) AS matched_objs
FROM test_me t
CONNECT BY LEVEL <= REGEXP_COUNT(t.text, 'obj[^,]*')
AND PRIOR t.ROWID = t.ROWID
AND PRIOR SYS_GUID() IS NOT NULL;

执行后就会直接返回你期望的obj_a, obj_b, obj_c, obj_d, obj结果。

拆分为每行一个匹配项的写法 #

如果需要对每个匹配项做后续处理，直接去掉LISTAGG聚合即可：

SELECT REGEXP_SUBSTR(t.text, 'obj[^,]*', 1, LEVEL) AS matched_obj
FROM test_me t
CONNECT BY LEVEL <= REGEXP_COUNT(t.text, 'obj[^,]*')
AND PRIOR t.ROWID = t.ROWID
AND PRIOR SYS_GUID() IS NOT NULL;

Oracle 12c 及以上优化方案 #

12c新增的LATERAL JOIN语法可以避免层级查询的潜在性能问题，写法更清晰：

SELECT LISTAGG(m.matched_obj, ', ') WITHIN GROUP (ORDER BY m.lvl) AS matched_objs
FROM test_me t
CROSS JOIN LATERAL (
    SELECT REGEXP_SUBSTR(t.text, 'obj[^,]*', 1, LEVEL) AS matched_obj, LEVEL AS lvl
    FROM dual
    CONNECT BY LEVEL <= REGEXP_COUNT(t.text, 'obj[^,]*')
) m;

自定义正则规则说明 #

你可以根据实际需求替换SQL中的正则表达式：

• 提取指定域名的邮箱：替换为[a-zA-Z0-9._%+-]+@your-domain\.com
• 提取SQL中的表名：根据你的命名规则调整正则即可
• 其他场景只需要修改REGEXP_SUBSTR和REGEXP_COUNT中的匹配模式即可复用整套逻辑。

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