17,377
社区成员
发帖
与我相关
我的任务
分享
create table tel (
lxdh varchar2(100),
tel1 varchar(11),
tel2 varchar(11),
phone varchar(20),
bz varchar(50)
);
insert into tel(lxdh) values('13811012343|01023456543|13212345432|');
insert into tel(lxdh) values('0451-4586754|02019857485|13811111111|');
insert into tel(lxdh) values('01023456543|13212345432|');
insert into tel(lxdh) values('01023456543');
insert into tel(lxdh) values('01023456543|13212345432|13411111111|13611111111|14511111111');
select * from tel;
WITH TEL AS (
SELECT '13811012343|01023456543|13212345432|' LXDH FROM DUAL UNION ALL
SELECT '0451-4586754|02019857485|13811111111|' FROM DUAL UNION ALL
SELECT '01023456543|13212345432|' FROM DUAL UNION ALL
SELECT '01023456543' FROM DUAL UNION ALL
SELECT '01023456543|13212345432|13411111111|13611111111|14511111111' FROM DUAL
)
SELECT
LXDH,
LTRIM(REGEXP_SUBSTR('|'||LXDH,'\|1[0-9]{10}',1,1),'|') TEL1,
LTRIM(REGEXP_SUBSTR('|'||LXDH,'\|1[0-9]{10}',1,2),'|') TEL2,
-- 下面的字段修改为取出去除前两个手机电话后的第1个电话号码
LTRIM(REGEXP_SUBSTR(REGEXP_REPLACE(REGEXP_REPLACE('|'||LXDH,'\|1[0-9]{10}','',1,1),'\|1[0-9]{10}','',1,1),'\|[^\|]+', 1,1),'|') PHONE,
LTRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE('|'||LXDH,'\|1[0-9]{10}','',1,1),'\|1[0-9]{10}','',1,1),'\|[^\|]+','',1,1),'|') BZ
FROM TEL;
-- 呵呵,还是写错了,再改正:
-- 将mobile_mem临时表中的mobile字段的其他记录合并到备注中:
with a as (select mobile, min(rn) as rn from mobile_mem group by mobile),
b as (select rn, mobile from a order by rn)
select replace(wm_concat(b.mobile),',','|') as bz into v_bz
from b;
-- 当然:如果要彻底去除重复记录(上面的存储过程,在bz字段的数据,可能还存在重复的手机号),
-- 可以修改这一部分:
-- 将mobile_mem临时表中的mobile字段的其他记录合并到备注中:
with a as (select mobile, min(rn) as rn from mobile_mem group by mobile),
b as (select rn, mobile from a order by rn)
select replace(wm_concat(a.mobile),',','|') as bz into v_bz
from a;
-- 注:varchar 是 Oracle 的一个过时的数据类型,现在我们习惯将所有 varchar 类型 用 varchar2 类型代替!
drop table tel purge;
create table tel (
lxdh varchar2(100),
tel1 varchar2(11),
tel2 varchar2(11),
phone varchar2(20),
bz varchar2(50)
);
insert into tel(lxdh) values('13811012343|01023456543|13212345432|');
insert into tel(lxdh) values('0451-4586754|02019857485|13811111111|');
insert into tel(lxdh) values('01023456543|13212345432|');
insert into tel(lxdh) values('01023456543');
insert into tel(lxdh) values('01023456543|13212345432|13411111111|13611111111|14511111111');
-- 先:创建一个临时表用以存放tel表每条记录的lxdh字段分割出来的号码!
CREATE GLOBAL TEMPORARY TABLE mobile_mem(
rn number(18,0), -- 按tel表中 v_lxdh中号码的顺序编号
mobile varchar2(200)
) ON COMMIT PRESERVE ROWS;
-- 创建存储过程,用以解析 tel 表中的 lxdh 字段,并将符合要求的数据更新到 tel1、tel2、phone、bz 字段中!
CREATE OR REPLACE PROCEDURE p_phone_ins
IS
v_rowid rowid;
v_lxdh tel.lxdh%type;
v_tel1 tel.tel1%type;
v_tel2 tel.tel2%type;
v_phone tel.phone%type;
v_bz tel.bz%type;
CURSOR cur is SELECT rowid, tel.lxdh FROM tel order by rowid;
BEGIN
for i in cur loop
v_rowid := i.rowid;
v_lxdh := i.lxdh||'|';
insert into mobile_mem(rn, mobile)
with a as ( select v_lxdh mobile from dual )
select level as rn, REGEXP_SUBSTR(a.mobile,'[^| ]+',1,level)
from a
connect by level<=length(v_lxdh)-length(replace(v_lxdh,'|',''));
-- 删除手机号为空的数据行:
delete from mobile_mem where mobile is null;
-- 初始化一些变量
v_tel1 := '';
v_tel2 := '';
v_phone := '';
v_bz := '';
-- 获取手机号,插入 v_tel1 变量
select a.mobile into v_tel1
from (select m.rn, m.mobile from mobile_mem m
where length(m.mobile)=11 -- 长度为 11 位
and m.mobile like '1%' -- 且手机号以 1 开头
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
-- 获取手机号,插入 v_tel2 变量
-- 如果存在符合要求的第一个手机号,则继续查找是否有符合要求的第二个手机号(如果第一个手机号码都获取不了(为空),就无需再查找一次啦)
if v_tel1 is not null then
begin
delete from mobile_mem where mobile=v_tel1; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
select a.mobile into v_tel2
from (select m.rn, m.mobile
from mobile_mem m
where m.mobile<>v_tel1 -- 排除第一次已经获取的号码
and length(m.mobile)=11 -- 长度为 11 位
and m.mobile like '1%' -- 且手机号以 1 开头
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
if v_tel2 is not null then
delete from mobile_mem where mobile=v_tel2; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
end if;
end;
end if;
-- 获取其他号码,插入 v_phone 变量
select a.mobile into v_phone
from (select m.rn, m.mobile from mobile_mem m
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
if v_phone is not null then
delete from mobile_mem
where mobile=v_phone; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
end if;
-- 将mobile_mem临时表中的mobile字段的其他记录合并到备注中:
with a as (select rn, mobile from mobile_mem order by rn)
select replace(wm_concat(a.mobile),',','|') as bz into v_bz
from a;
-- 更新原表记录:(当然:你也可以把每次分析后的 rowid、v_tel1、t_tel2、v_phone、v_bz 插入到另外一个临时表,最后一次性用rowid关联更新!
update tel
set tel1=v_tel1, tel2=v_tel2, phone=v_phone, bz=v_bz
where rowid=v_rowid;
dbms_output.put_line('v_tel1: '||v_tel1||' v_tel2: '||v_tel2||' v_phone: '||v_phone||' v_bz: '||v_bz);
-- 本条记录解析完毕,删除临时表中的记录
DELETE FROM mobile_mem;
-- 提交
COMMIT;
end loop;
END;
/
-- 执行存储过程:
set serveroutput on;
exec p_phone_ins();
-- 验证数据:
select * from tel;
-- 注:varchar 是 Oracle 的一个过时的数据类型,现在我们习惯将所有 varchar 类型 用 varchar2 类型代替!
drop table tel purge;
create table tel (
lxdh varchar2(100),
tel1 varchar2(11),
tel2 varchar2(11),
phone varchar2(20),
bz varchar2(50)
);
insert into tel(lxdh) values('13811012343|01023456543|13212345432|');
insert into tel(lxdh) values('0451-4586754|02019857485|13811111111|');
insert into tel(lxdh) values('01023456543|13212345432|');
insert into tel(lxdh) values('01023456543');
insert into tel(lxdh) values('01023456543|13212345432|13411111111|13611111111|14511111111');
-- 先:创建一个临时表用以存放tel表每条记录的lxdh字段分割出来的号码!
CREATE GLOBAL TEMPORARY TABLE mobile_mem(
rn number(18,0), -- 按tel表中 v_lxdh中号码的顺序编号
mobile varchar2(200)
) ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE PROCEDURE p_phone_ins
IS
v_rowid rowid;
v_lxdh tel.lxdh%type;
v_tel1 tel.tel1%type;
v_tel2 tel.tel2%type;
v_phone tel.phone%type;
v_bz tel.bz%type;
CURSOR cur is SELECT rowid, tel.lxdh FROM tel order by rowid;
BEGIN
for i in cur loop
v_rowid := i.rowid;
v_lxdh := i.lxdh||'|';
insert into mobile_mem(rn, mobile)
with a as ( select v_lxdh mobile from dual )
select level as rn, REGEXP_SUBSTR(a.mobile,'[^| ]+',1,level)
from a
connect by level<=length(v_lxdh)-length(replace(v_lxdh,'|',''));
-- 删除手机号为空的数据行:
delete from mobile_mem where mobile is null;
-- 初始化一些变量
v_tel1 := '';
v_tel2 := '';
v_phone := '';
v_bz := '';
-- 获取手机号,插入 v_tel1 变量
select a.mobile into v_tel1
from (select m.rn, m.mobile from mobile_mem m
where length(m.mobile)=11 -- 长度为 11 位
and m.mobile like '1%' -- 且手机号以 1 开头
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
-- 获取手机号,插入 v_tel2 变量
-- 如果存在符合要求的第一个手机号,则继续查找是否有符合要求的第二个手机号(如果第一个手机号码都获取不了(为空),就无需再查找一次啦)
if v_tel1 is not null then
begin
delete from mobile_mem where mobile=v_tel1; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
select a.mobile into v_tel2
from (select m.rn, m.mobile
from mobile_mem m
where m.mobile<>v_tel1 -- 排除第一次已经获取的号码
and length(m.mobile)=11 -- 长度为 11 位
and m.mobile like '1%' -- 且手机号以 1 开头
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
if v_tel2 is not null then
delete from mobile_mem where mobile=v_tel2; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
end if;
end;
end if;
-- 获取其他号码,插入 v_phone 变量
select a.mobile into v_phone
from (select m.rn, m.mobile from mobile_mem m
union all
select 999999999 as rn, '' as mobile from dual
order by rn asc ) a
where rownum<=1;
if v_phone is not null then
delete from mobile_mem
where mobile=v_phone; -- 手机号一旦获取,就可以从临时表中删除这个号码啦
end if;
-- 将mobile_mem临时表中的mobile字段的其他记录合并到备注中:
with a as (select rn, mobile from mobile_mem order by rn)
select replace(wm_concat(a.mobile),',','|') as bz into v_bz
from a;
-- 更新原表记录:(当然:你也可以把每次分析后的 rowid、v_tel1、t_tel2、v_phone、v_bz 插入到另外一个临时表,最后一次性用rowid关联更新!
update tel
set tel1=v_tel1, tel2=v_tel2, phone=v_phone, bz=v_bz
where rowid=v_rowid;
dbms_output.put_line('v_tel1: '||v_tel1||' v_tel2: '||v_tel2||' v_phone: '||v_phone||' v_bz: '||v_bz);
-- 本条记录解析完毕,删除临时表中的记录
DELETE FROM mobile_mem;
-- 提交
COMMIT;
end loop;
END;
/
-- 执行存储过程:
set serveroutput on;
exec p_phone_ins();
-- 验证数据:
select * from tel;
select lxdh,tel1,tel2,phone2, case when length(nvl(phone1,tel3||'|'||tel4))<10 then replace(nvl(phone1,tel3||'|'||tel4),'|') else nvl(phone1,tel3||'|'||tel4) end BZ
from
(
select lxdh,nvl(nvl(tel1,tel3),tel4) tel1,nvl(tel2,tel4) tel2,
case when tel1<>tel3 then tel3 else ''end tel3,
case when tel4<>tel2 then tel4 else ''end tel4,
phone1,phone2
from
(
select lxdh,max(case when rn=4 and substr(str2,1,1)='1' then str2 end) tel1,
max(case when rn=3 and substr(str2,1,1)='1' then str2 end) tel2,
max(case when rn=2 and substr(str2,1,1)='1' then str2 end) tel3,
max(case when rn=1 and substr(str2,1,1)='1' then str2 end) tel4,
max(case when rn=1 and substr(str2,1,1)='0' then str2 end) phone1,
max(case when rn=0 and substr(str2,1,1)='0' then str2 end) phone2
from
(
select lxdh,str2,row_number()over(partition by lxdh order by substr(str2,1,2))-1 rn
from
(
select lxdh,str2,m from
(
select distinct lxdh,str2,m from
(
select lxdh, substr(lxdh, instr(lxdh,'|',1,level)+1,
instr(lxdh,'|',1,level+1)-instr(lxdh,'|',1,level)-1) as str2
,level m
from tel connect by level <=4
) a
union all
select distinct lxdh,substr(lxdh, 1,instr(lxdh,'|',1)-1) str1,0
from tel
) where str2 is not null
order by lxdh
)
order by lxdh
)
group by lxdh
)
)
--result:
1 01023456543| 01023456543
2 01023456543|13212345432| 13212345432 13212345432 01023456543
3 01023456543|13212345432|13411111111|13611111111|14511111111| 14511111111 13611111111 01023456543 13411111111|13212345432
4 0451-4586754|02019857485|13811111111| 13811111111 02019857485 0451-4586754
5 13811012343|01023456543|13212345432| 13811012343 13212345432 01023456543
WITH TEL AS (
SELECT '13811012343|01023456543|13212345432|' LXDH FROM DUAL UNION ALL
SELECT '0451-4586754|02019857485|13811111111|' FROM DUAL UNION ALL
SELECT '01023456543|13212345432|' FROM DUAL UNION ALL
SELECT '01023456543' FROM DUAL UNION ALL
SELECT '01023456543|13212345432|13411111111|13611111111|14511111111' FROM DUAL
)
SELECT
LXDH,
LTRIM(REGEXP_SUBSTR('|'||LXDH,'\|1[0-9]{10}',1,1),'|') TEL1,
LTRIM(REGEXP_SUBSTR('|'||LXDH,'\|1[0-9]{10}',1,2),'|') TEL2,
LTRIM(REGEXP_SUBSTR(REGEXP_REPLACE('|'||LXDH,'\|1[0-9]{10}',''), '[^\|]+',1),'|') PHONE,
LTRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE('|'||LXDH,'\|1[0-9]{10}','',1,1),'\|1[0-9]{10}','',1,1),'\|[^\|]+','',1,1),'|') BZ
FROM TEL;
-- 执行结果
1 13811012343|01023456543|13212345432| 13811012343 13212345432 01023456543
2 0451-4586754|02019857485|13811111111| 13811111111 0451-4586754 02019857485|
3 01023456543|13212345432| 13212345432 01023456543
4 01023456543 01023456543
5 01023456543|13212345432|13411111111|13611111111|14511111111 13212345432 13411111111 01023456543 13611111111|14511111111