Oracle中匹配一个字段中有多个字符匹配的解决方法

1、问题例子

现在需要对表中的（如下图中红色框的字符串）字典码，转化成值。
如：D0801–> 虚假电话。

转化后的结果是 ：

2、解决方法
把目标表变成：

select apply_id,
regexp_substr(third_code, ‘[^,]+’, 1, level, ‘i’) as third_code
from (select *
from sr_kx_sn.SNTBL_BUS_DECISION_CODE
where FIRST_CODE = ‘D’)
connect by level <= regexp_count(third_code, ‘,’) + 1
and (prior apply_id) = apply_id
and (prior dbms_random.value()) is not null

结果如上图所示：
然后，连接词典表，把编码换成值在还原到原表中。
select a.apply_id,b.dic_value from
(select apply_id,
regexp_substr(third_code, ‘[^,]+’, 1, level, ‘i’) as third_code
from (select *
from sr_kx_sn.SNTBL_BUS_DECISION_CODE
where FIRST_CODE = ‘D’)
connect by level <= regexp_count(third_code, ‘,’) + 1
and (prior apply_id) = apply_id
and (prior dbms_random.value()) is not null) a left join sr_kx_sn.SNTBL_SYS_DECISION_DICTIONARY b on a.Third_code=b.DIC_CODE
结果如下：

然后把表合起来：
with dic_value as(
select a.apply_id,b.dic_value,row_number() over(partition by a.apply_id order by a.third_code ) as flag from
(select apply_id,
regexp_substr(third_code, ‘[^,]+’, 1, level, ‘i’) as third_code
from (select *
from sr_kx_sn.SNTBL_BUS_DECISION_CODE
where FIRST_CODE = ‘D’)
connect by level <= regexp_count(third_code, ‘,’) + 1
and (prior apply_id) = apply_id
and (prior dbms_random.value()) is not null) a left join sr_kx_sn.SNTBL_SYS_DECISION_DICTIONARY b on a.Third_code=b.DIC_CODE
)

select apply_id,
substr(sys_connect_by_path(dic_value, ‘,’), 2) as SECOND_CODE
from dic_value
where connect_by_isleaf = 1
start with flag = 1
connect by (prior flag) = flag - 1
and (prior apply_id) = apply_id

结果就出来了：

3、注意的地方：
下图中的红框字符是关键词，不能改到