17,081
社区成员
发帖
与我相关
我的任务
分享
select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-2) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),3,null,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-3)) MGR,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1) A_COUNT,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) U_ACCOUNT
from T
select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-2) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),3,null,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-3)) MGR,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1) EMPNO A_COUNT,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) EMPNO U_ACCOUNT
from T
-- 凑个人数,放到一个字段中,LZ 自己拆分吧。
with mt as (
select '188888,0,13' rn from dual union all
select '188888,1,214' from dual union all
select '188888\,2000000,0,1' from dual union all
select '188888\,2000000,1,2' from dual union all
select '188888\,2000000,1,1' from dual union all
select '188888\,2000000\,300000,1,2' from dual union all
select '188888\,200000\,300000\,400000,1,1' from dual union all
select '188888\,200000\,300000\,400000\,500000,1,1' from dual
)
select
reverse(substr(reverse(rn),0, (case when instr(reverse(rn),',',1,4) = 0 then 4000 else instr(reverse(rn),',',1,4) - 1 end ) )) text
from mt
select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),1,null,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1)) MGR
,A_COUNT,U_ACCOUNT
from T