-- 凑个人数,放到一个字段中,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
-- 凑个人数,放到一个字段中,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]+')-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
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