oracle正则表达式截取字符串

[lou3578]

求大佬帮帮忙

[一名即将失业的db]

select regexp_substr(remark, '[^(国家税务局)]+[^(税务局)]', 1, 1),
       regexp_replace(remark, '国家税务局|税务局'),
       replace(replace(remark, '国家税务局'), '税务局')
  from (select '国家税务局湖北税务局' as remark
          from dual
        union
        select '国家税务局上海市陆家嘴税务局' as remark
          from dual
        union
        select '国家税务局杭州市税务税务局' as remark
          from dual)

[nayi_224]

select regexp_substr(remark, '[^(国家税务局)]+[^(税务局)]+', 1, 1),

[sallkey]

with tmp as ( select '国家税务局湖北税务局' as remark from dual union select '国家税务局广西壮族自治区税务局' as remark from dual union select '国家税务局黑龙江省税务税务局' as remark from dual) select regexp_substr(t.remark, '[ ^(国家税务局)]+[^(税务局)]+', 1, 1) from tmp t;