Oracle使用REGEXP提取单列中所有匹配的C-、WHT前缀计费编号记录

现有查询存在两个核心问题：

• 层级计数逻辑错误：原有通过REGEXP_REPLACE计算层级上限的逻辑无法准确统计符合条件的匹配项总数，会出现漏匹配、多生成空行的问题
• 未做数值提取：仅匹配到了C-/WHT开头的完整行，没有剔除前缀提取纯数字部分

SELECT REGEXP_SUBSTR(STR, '^(C-|WHT)(\d+)$', 1, LEVEL, 'm', 2) AS CHARGE_NUMBER
FROM
(
  SELECT DBMS_LOB.SUBSTR(COL_DATA, 4000, 1) AS STR, TRANX_DATE, USER_ID
  FROM  TABLE_1 
)
CONNECT BY LEVEL <= REGEXP_COUNT(STR, '^(C-|WHT)\d+$', 1, 'm')
AND PRIOR STR = STR
AND PRIOR SYS_GUID() IS NOT NULL
WHERE REGEXP_SUBSTR(STR, '^(C-|WHT)(\d+)$', 1, LEVEL, 'm', 2) IS NOT NULL;

• 用REGEXP_COUNT直接统计符合^C-|^WHT规则的行总数作为LEVEL上限，计数精准不会出现多算漏算
• 正则调整为分组捕获规则，直接提取前缀后的纯数字部分作为CHARGE_NUMBER，无需二次处理
• 新增CONNECT BY防重复条件，避免单条COL_DATA拆分时产生多余重复行
• 增加空值过滤条件，排除迭代过程中产生的无效空行

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