使用 regexp_substr + connect by 实现;
举个例子,希望大家能看明白,如果觉得有帮助就点个赞吧~~
表名:WP_1215_BM02 字段:type_id ,deptname
数据如图:
需要实现:分割deptname为两行,对应相同的type_id
sql:
with t as
(select *
from WP_1215_BM02--表
)
select type_id,regexp_substr(deptname, '[^、]+', 1, level)
from t
connect by level <= regexp_count(deptname, '\、\') + 1
and deptname = prior deptname
and prior dbms_random.value > 0
运行效果: