Oracle中使用Connect by Level结合regexp_substr返回所有匹配记录的方法

你当前写法的核心问题是误用LENGTH()包裹REGEXP_COUNT的返回结果。REGEXP_COUNT本身会直接返回目标字符串匹配正则规则的总次数，你额外加LENGTH()相当于把匹配次数的数字的字符长度当作了循环上限：比如你示例数据里共匹配到10次，REGEXP_COUNT返回数值10，LENGTH(10)的结果是2，所以循环上限是2+1=3，自然只能返回前3条匹配结果，和你观察到的现象完全一致。

正确查询语句如下 #

SELECT REGEXP_SUBSTR(DATA_SOURCE,'A-\S+|ABC\S+',1, LEVEL) AS REF_NUM
FROM
(
SELECT '
Z-TEST
Y-TEST
A-123456789
ABC123456790
Y-TRY
A-123456791
ABC123456792
ABC123456793
Y-TRY
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456794
ABC123456795
ABC123456796
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456797
ABC123456798
' DATA_SOURCE
FROM DUAL
)
CONNECT BY 
  LEVEL <= REGEXP_COUNT(DATA_SOURCE,'A-\S+|ABC\S+')
  -- 避免单条数据查询时出现循环报错
  AND PRIOR SYS_GUID() IS NOT NULL
  AND PRIOR DATA_SOURCE IS NOT NULL
-- 过滤空匹配结果
WHERE REGEXP_SUBSTR(DATA_SOURCE,'A-\S+|ABC\S+',1, LEVEL) IS NOT NULL;

方案优势 #

• 完全不需要设置固定数值，循环上限会自动根据实际匹配次数动态调整，后续匹配数量增加也不需要修改代码
• 执行效率和固定上限写法一致，无额外性能损耗
• 新增的防循环判断适配单条/多条数据源的查询场景
  如果需要同时保留DATA_SOURCE字段，直接在SELECT子句中添加即可，不影响逻辑。

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