向oracle正则表达式高手求救

怎样用select实现：凡是“名字”中含有连续两个L的，单独一列，并且这一列只有包换LL的那个单词。如果没有，显示null
比如表：
名字价格
ab sdLLddd 40
ab sdf 50

用过select语句后的结果：
名字短写
ab sdLLddd sdLLddd
ab sdf null

因为是初学，所以写得有点乱

构造表：

create table tb(tname varchar2(20),tprice number);

insert into tb values('sdf sf LLsd',30);

insert into tb values('dsLL sdf dsf',40);

insert into tb values('ds dLLll dsfl',50);

insert into tb values('LL',60);

insert into tb values(' LL',70);

insert into tb values('ds sdf dsf',80);

insert into tb values('ds dl dsfl',90);

执行命令：

select c1 名字

,decode(substr(c1,c2+1,c3-c2),'','NULL',substr(c1,c2+1,c3-c2)) 短写

from (select b1 c1

,decode(b2-b3,0,0,instr(b1,' ',1,b2-b3)) c2

,decode(b2-b3-b4+b5,0,b4,instr(b1,' ',1,b2-b3+1)) c3

from (select tname b1

,length(substr(tname,1,instr(tname,'LL',1,1))) b2

,length(replace(substr(tname,1,instr(tname,'LL',1,1)),' ','')) b3

,length(tname) b4

,length(replace(tname,' ','')) b5

from tb

)

);

热门标签