Oracle SQL中使用REGEXP_SUBSTR拆分字符串出错问题排查

问题根源 #

原SQL里用的正则表达式[^ '||v_delimiter||']+只匹配非分隔符的连续字符，没法识别分隔符开头的空字符串。拿|yyyy-mm-dd来说，第一个元素是空值，这个正则匹配不到，导致：

• level=1时，v_format返回null
• level=2时，v_format返回yyyy-mm-dd
  和预期的字段对应关系完全颠倒。

修正后的SQL #

调整正则表达式，让它能匹配分隔符之间的空元素，同时保留原有拆分逻辑：

with params as (
  select  'ID|DUE_DATE'         as v_names
         ,'80781|2026-12-01'    as v_values
         ,'VARCHAR2|DATE'       as v_types
         ,'|yyyy-mm-dd'         as v_types_format
         ,'|' as v_delimiter
  from dual
)
SELECT
   REGEXP_SUBSTR(v_names, '(^|'||v_delimiter||')([^'||v_delimiter||']*)', 1, level, null, 2) AS v_name
  ,REGEXP_SUBSTR(v_values, '(^|'||v_delimiter||')([^'||v_delimiter||']*)', 1, level, null, 2) AS v_value
  ,REGEXP_SUBSTR(v_types, '(^|'||v_delimiter||')([^'||v_delimiter||']*)', 1, level, null, 2) AS v_type
  ,REGEXP_SUBSTR(v_types_format, '(^|'||v_delimiter||')([^'||v_delimiter||']*)', 1, level, null, 2) as v_format
FROM params
CONNECT BY REGEXP_SUBSTR(v_names, '(^|'||v_delimiter||')([^'||v_delimiter||']*)', 1, level, null, 2) IS NOT NULL

关键修改说明 #

1. 正则表达式调整：(^|'||v_delimiter||')([^'||v_delimiter||']*)的逻辑：
   • (^|'||v_delimiter||')：匹配字符串开头或分隔符
   • ([^'||v_delimiter||']*)：匹配分隔符之间的任意内容（包括空字符串）
2. REGEXP_SUBSTR参数：第6个参数设为2，表示提取第二个分组的内容，也就是分隔符之间的目标元素。

执行结果 #

完全符合预期的字段对应关系。

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