F_66654_66908_6033500003

Question

数据库的表中，有一列数据是这样的：F_66654_66908_6033500003  还有的是这样的：A_123_543  也就是说，这列有的有2个_有的有3个_ 我想实现这样的结果，对于F_66654_66908_6033500003  我要66654和66908；对于A_123_543  我要123和543。用什么样的sql语句能实现啊？急，在线等，谢谢各位！！！

Answer 1

用instr，substr可以做到。还要用case when来判断有几个_

Answer 2

这里有个例子，你看下。对了把里面的lpad函数去掉，就不会前面给补0或Z了。：drop table a;
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;