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、注意的地方:
下图中的红框字符是关键词,不能改到