数据库的表中,有一列数据是这样的:F_66654_66908_6033500003 还有的是这样的:A_123_543 也就是说,这列有的有2个_有的有3个_ 我想实现这样的结果,对于F_66654_66908_6033500003 我要66654和66908;对于A_123_543 我要123和543。用什么样的sql语句能实现啊?急,在线等,谢谢各位!!!
调试欢乐多
create table a (id number(10),name varchar2(10));
insert into a select 1,'甲' from dual union select 2,'乙' from dual;
insert into a select 3,'丙' from dual union select 4,'丁' from dual;
drop table b;
create table b(name varchar2(20 char));
insert into b values ('5-49-16-丁');
insert into b values ('5-49-16-丙');
insert into b values ('4-49-40-甲');
insert into b values ('4-49-55-丁');
insert into b values ('5-47-55-丁');
insert into b values ('12-51-(20)');
insert into b values ('12-51-(20)-0');
insert into b values ('5-48-16-丙');
insert into b values ('G-48-21-B');
insert into b values ('(G)-48-21-B');
insert into b values ('G-(48)-21-B');
insert into b values ('5-49-16-丙');
insert into b values ('12-51-20');
insert into b values ('12-51-10');
insert into b values ('(12)-51-10-(a)');
insert into b values ('M-52-10-(a)');
insert into b values ('J46E016015');
insert into b values ('K45E013007');
insert into b values ('K45E014007');
insert into b values ('543');
insert into b values ('ss');
commit;select (case when
instr(substr(name,1,instr(name,'-')-1),'(')>0 --第1个中包含()的,按字符串格式化为8位,前补Z
or instr(substr(name,1,instr(name,'-')-1),')')>0
then
lpad(substr(name,1,instr(name,'-')-1),8,'Z')
when
upper(substr(name,1,instr(name,'-')-1))= lower(substr(name,1,instr(name,'-')-1)) --是数字,格式化为8位,前补0
then
to_char(to_number(substr(name,1,instr(name,'-')-1)),'00000000')
else
lpad(substr(name,1,instr(name,'-')-1),8,'Z') --字符串格式化为8位,前补Z
end)
as col1,
(case when
instr(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1),'(')>0 --第2个中包含()的,按字符串格式化为8位,前补Z
or instr(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1),')')>0
then
lpad(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1),8,'Z')
when
upper(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1))=
lower(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1)) --是数字,格式化为8位,前补0
then
to_char(to_number(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1)),'00000000')
else
lpad(substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-instr(name,'-',1,1)-1),8,'Z') --字符串格式化为8位,前补Z
end)
as col2,
(case when
instr(name,'-',1,3)=0 --不存在第3个-的,直接取最后那段,即第3段,
and instr(substr(name,instr(name,'-',-1,1)+1),'(')>0 --第3个中包含()的,按字符串格式化为8位,前补Z
and instr(substr(name,instr(name,'-',-1,1)+1),')')>0
then
lpad(substr(name,instr(name,'-',-1,1)+1),8,'Z')
when
instr(name,'-',1,3)=0 --不存在第3个-的,直接取最后那段,即第3段,是数字按字符串格式化为8位,前补0
and upper(substr(name,instr(name,'-',-1,1)+1)) = lower(substr(name,instr(name,'-',-1,1)+1))
then
lpad(substr(name,instr(name,'-',-1,1)+1),8,'0')
when
instr(name,'-',1,3)=0 --不存在第3个-的其他的,按字符串格式化为8位,前补Z
then
lpad(substr(name,instr(name,'-',-1,1)+1),8,'Z')
when
instr(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1),'(')>0 --第3个中包含()的,按字符串格式化为8位,前补Z
or instr(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1),')')>0
then
lpad(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1),8,'Z')
when
upper(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1))=
lower(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1)) --是数字,格式化为8位,前补0
then
to_char(to_number(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1)),'00000000')
else
lpad(substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1),8,'Z') --字符串格式化为8位,前补Z
end)
as col3,
(case when
instr(b.name,'-',1,3)=0 --不存在第3个-的,直接设为最大
then
'ZZZZZZZZ'
when
instr(substr(name,instr(name,'-',-1,1)+1),'(')>0 --第4个中包含()的,按字符串格式化为8位
or instr(substr(name,instr(name,'-',-1,1)+1),')')>0
then
lpad((substr(name,instr(name,'-',-1,1)+1)),8,'Z')
when
exists(select 1 from a where a.name=substr(b.name,instr(b.name,'-',-1,1)+1)) --存在对照表的排序的,取序号,格式化为8位,前补0
then
(select to_char(a.id,'00000000') from a where a.name=substr(b.name,instr(b.name,'-',-1,1)+1) )
when
upper(substr(name,instr(name,'-',-1,1)+1))= lower(substr(name,instr(name,'-',-1,1)+1)) --是数字,格式化为8位,前补0
then
lpad((substr(name,instr(name,'-',-1,1)+1)),8,'9')
else
lpad((substr(name,instr(name,'-',-1,1)+1)),8,'Z') --其他不在对照表的,按字符串格式化为8位
end)
as col4,
name from b order by col1,col2,col3,col4;insert into b values ('I10asdf');
insert into b values ('I09asdf');
commit;select (case when
instr(name,'-')<=0
and (instr(name,'(')>0 --不包含-但包含()的,前补Z,格式化为8位
or instr(name,')')>0)
then
lpad(substr(name,1,1),8,'Z')
when
instr(name,'-')<=0
and upper(name)= lower(name) --不包含-全数字的,前补0,格式化为8位
then
lpad(name,8,'0')
when
instr(name,'-')<=0 --不包含-的其他,前补Z,格式化为8位
then
lpad(substr(name,1,1),8,'Z')
when
instr(substr(name,1,instr(name,'-')-1),'(')>0 --第1个中包含()的,按字符串格式化为8位,前补Z
or instr(substr(name,1,instr(name,'-')-1),')')>0
then
lpad(substr(name,1,instr(name,'-')-1),8,'Z')
when
upper(substr(name,1,instr(name,'-')-1))= lower(substr(name,1,instr(name,'-')-1)) --是数字,格式化为8位,前补0
then
lpad(substr(name,1,instr(name,'-')-1),8,'0')
else
lpad(substr(name,1,instr(name,'-')-1),8,'Z') --字符串格式化为8位,前补Z
end)
as col1,name from b order by col1,name;