--NB的 case whencreate table test0518(n1 date,n2 varchar2(50));insert into test0518 values(sysdate,'0.11 0.12 0.11 0.14')insert into test0518 values(sysdate,'0.11');select n2,INSTR(n2, '0.11', 1, 1),INSTR(n2, '0.11', 1, 2) from test0518 ;--第一次修改 屏蔽重复出现的字符串 SELECT n1,n2,CASE WHEN INSTR(n2, '0.11', 1, 2) > 0 THEN SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) - 1)|| REPLACE(SUBSTR(n2,INSTR(n2, '0.11', 1, 2),LENGTH(n2)),'0.11','')else n2 end as xxxx from test0518;N1 N2 XXXX------------------ ------------------------- -----------------2017/5/19 9:36:09 0.12 ,0.13 ,0.11 ,0.11 0.12 ,0.13 ,0.11 ,2017/5/18 9:26:06 0.11 ,0.12 ,0.11 ,0.14 0.11 ,0.12 , ,0.142017/5/18 9:26:09 0.11 0.112017/5/18 9:56:09 0.12 ,0.11 ,0.11 ,0.13 0.12 ,0.11 , ,0.13结果有点瑕疵，0.11 去掉了 但是剩下了 多余的 逗号--第二次修改 去除多余的逗号 SELECT n1,n2,CASE WHEN INSTR(n2, '0.11', 1, 2) > 0 THEN --如果第二次出现的位置(13)>0 ，那么重复出现了 0.11 ,0.12 ,0.11 ,0.14 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) - 1)|| --从左边一次性的截取到 13-1 位置 即：0.11 ,0.12 ,REPLACE(SUBSTR(n2,INSTR(n2, '0.11', 1, 2)/*13*/,LENGTH(n2)),'0.11 ,','')else n2 end as xxxx from test0518;N1 N2 XXXX------------------ ------------------------- ------------------------2017/5/19 9:36:09 0.12 ,0.13 ,0.11 ,0.11 0.12 ,0.13 ,0.11 ,0.112017/5/18 9:26:06 0.11 ,0.12 ,0.11 ,0.14 0.11 ,0.12 ,0.142017/5/18 9:26:09 0.11 0.112017/5/18 9:56:09 0.12 ,0.11 ,0.11 ,0.13 0.12 ,0.11 ,0.13 --第三次修改 改为 update caseupdate test0518 set n5=CASE WHEN INSTR(n2, '0.11', 1, 2) > 0 THEN --如果第二次出现的位置(13)>0 ，那么重复出现了 0.11 ,0.12 ,0.11 ,0.14 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) - 1)|| --从左边一次性的截取到 13-1 位置 即：0.11 ,0.12 ,REPLACE(SUBSTR(n2,INSTR(n2, '0.11', 1, 2)/*13*/,LENGTH(n2)),'0.11 ,','')else n2 end ;--第四次修改 SELECT n1,n2,CASE WHEN INSTR(n2, '0.11', 1, 2) > 0 AND INSTR(n2, '0.11', 1, 2)< LENGTH(N2)-3 THEN --0.11 ,0.12 ,0.11 ,0.14 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) - 1)|| REPLACE(SUBSTR(n2,INSTR(n2, '0.11', 1, 2)+6,LENGTH(n2)),'0.11,','') WHEN INSTR(n2, '0.11', 1, 2) > 0 AND INSTR(n2, '0.11', 1, 2)=LENGTH(N2)-3 THEN --0.12 ,0.13 ,0.11 ,0.11 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) -2 )else N2 end as COL3 --0.11 from test0518;N1 N2 XXXX------------------ ------------------------- ------------------------2017/5/19 9:36:09 0.12 ,0.13 ,0.11 ,0.11 0.12 ,0.13 ,0.11 2017/5/18 9:26:06 0.11 ,0.12 ,0.11 ,0.14 0.11 ,0.12 ,0.142017/5/18 9:26:09 0.11 0.112017/5/18 9:56:09 0.12 ,0.11 ,0.11 ,0.13 0.12 ,0.11 ,0.13--第五次修改 改为 update caseupdate test0518 set n6=CASE WHEN INSTR(n2, '0.11', 1, 2) > 0 AND INSTR(n2, '0.11', 1, 2)< LENGTH(N2)-3 THEN --0.11 ,0.12 ,0.11 ,0.14 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) - 1)|| REPLACE(SUBSTR(n2,INSTR(n2, '0.11', 1, 2)+6,LENGTH(n2)),'0.11,','') WHEN INSTR(n2, '0.11', 1, 2) > 0 AND INSTR(n2, '0.11', 1, 2)=LENGTH(N2)-3 THEN --0.12 ,0.13 ,0.11 ,0.11 SUBSTR(n2, 1, INSTR(n2, '0.11', 1, 2) -2 )else N2 end ;
转载于:https://www.cnblogs.com/iyoume2008/p/6875431.html
