Oracle使用REGEXP_SUBSTR与CONNECT BY LEVEL拆分字符串遇重复行问题

你的查询出现大量重复行，核心原因是CONNECT BY子句没有限定层级生成的范围，导致不同ID的行在生成层级时产生交叉关联，最终出现笛卡尔积式的重复。

修复后的查询代码 #

with qry as (
select 1 as id, '<div>What would you like to do today?<a href="#" data-jump="111">Client Service</a><a href="#" data-jump="112">Customer Service</a><a href="#" data-jump="113">Tecnical Service</a></div>' as html_string from dual
union
select 2 as id, '<div>What would you like to do today?<a href="#" data-jump="111">Client Service</a><a href="#" data-jump="112">Customer Service</a><a href="#" data-jump="113">Tecnical Service</a></div><a href="#" data-jump="114">Other Service</a></div>' as html_string from dual
)
SELECT ID,
REGEXP_SUBSTR(html_string, '<a[^>]*>(.*?)</a>', 1, LEVEL, NULL, 1) as contents,
REGEXP_SUBSTR(html_string, 'data-jump="(.*?)"', 1, LEVEL, NULL, 1) as data_jump
FROM qry
CONNECT BY LEVEL <= REGEXP_COUNT(html_string, '<a[^>]*>(.*?)</a>')
-- 关键修复条件
AND PRIOR id = id
AND PRIOR SYS_GUID() IS NOT NULL;

关键修改说明 #

• PRIOR id = id：强制层级生成仅在当前ID对应的行内进行，避免不同ID的行之间产生交叉关联，从根源上消除跨行重复。
• PRIOR SYS_GUID() IS NOT NULL：防止Oracle因多行存在相同的html_string内容而触发循环连接（SYS_GUID()每次生成唯一值，确保PRIOR条件不会匹配到其他行）。
• 正则表达式优化：将<a.*?>改为<a[^>]*>，避免匹配到包含a字符的其他标签（比如<div class="a-test">），提升匹配准确性。

生产环境性能优化建议 #

• 若表中数据量较大，建议对html_string列建立基于REGEXP_COUNT的函数索引，减少层级计算的开销。
• 若HTML格式相对固定，可考虑提前将解析后的结果存储到辅助表中，避免每次查询都执行正则解析。

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