3,490
社区成员
发帖
与我相关
我的任务
分享
insert into new_customer_mobile
select new_customer_mobile.nextval, id, mobile
from (select a.id,
substr(a.mobile,
instr('/' || a.mobile, '/', 1, b.rn),
instr(a.mobile || '/', '/', 1, b.rn) -
instr('/' || a.mobile, '/', 1, b.rn)) mobile
from old_customer a,
(select *
from (SELECT DISTINCT id FROM old_customer),
(SELECT ROWNUM rn
FROM dual
CONNECT BY ROWNUM <=
(select max(length(mobile) -
length(replace(mobile, '/'))) + 1
from old_customer))) b
WHERE A.ID = b.ID
and length(a.mobile) - length(replace(a.mobile, '/', '')) + 1 >= b.rn)
WITH A AS(SELECT 1 id,'1382299860/136788965' item FROM dual
UNION ALL SELECT 1,'131999987/132999935' FROM dual
union all select 2,'1357778840/1366778868/150998870' from dual)
select a.id,substr(a.item,instr('/'||a.item,'/',1,b.rn),instr(a.item||'/','/',1,b.rn)-instr('/'||a.item,'/',1,b.rn))item
from a,
(select * from
(SELECT DISTINCT id FROM A),
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=(
select max(length(item)-length(replace(item,'/')))+1 from a)))b
WHERE A.ID=b.ID
and length(a.item)-length(replace(a.item,'/',''))+1>=b.rn