Oracle正则表达式提取问题：多NO.场景结果不符合预期

要解决提取所有"NO."后子串并在多匹配时用" - "连接的问题，可通过嵌套正则替换实现：

with test (id, col) as (
  select 1, 'JOHN NO.10A 1234 TEST1'  from dual union all
  select 2, 'JOHN NO.10 1234 TEST1'  from dual union all
  select 3, 'JOHN NO.10A 1234 TEST1'  from dual union all
  select 4, 'JOHN NO.10A TEST1 - JOHN NO.20A TEST2'      from dual union all
  select 5, 'JOHN NO.10 TEST1 - JOHN NO.20 TEST2'      from dual union all
  select 6, 'JOHN NO.10A TEST1234'  from dual union all
  select 7, 'JOHN 10A TEST1234'   from dual
)
select
  id,
  col,
  nullif(
    trim(
      regexp_replace(
        regexp_replace(col, '.*?(NO\.(\S+))|.', '\2 ', 1, 0, 'i'),
        '\s+', ' - '
      )
    ), ''
  ) as expected
from test;

逻辑说明 #

• 第一层regexp_replace：遍历字符串，捕获所有NO.后的非空白子串，其余内容替换为空格
• 第二层regexp_replace：将连续空格替换为-，实现多匹配结果的连接
• trim：清理首尾多余空格
• nullif：将无匹配时的空字符串转为Null
• 'i'参数：忽略大小写，兼容NO./no.等写法

输出结果 #

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