SQL> create or replace function fun_One(vStr varchar2)
2 return varchar2 as
3 vTmp varchar2(100):='';
4 vRtn varchar2(100):='';
5 iTmp int:=0;
6 begin
7 vTmp := vStr;
8 while Instr(vTmp,'1')>0 loop
9 vTmp := vTmp ||','|| (Instr(vTmp,'1')+iTmp);
10 iTmp := iTmp + Instr(vTmp,'1');
11 vTmp := substr(vTmp,Instr(vTmp,'1')+1);
12 end loop;
13 return vTmp;
14 end fun_One;
15 /函数已创建。SQL> select fun_One('0010111') from dual;FUN_ONE('0010111')
------------------------------------------------------
,3,5,6,7
2 return varchar2 as
3 vTmp varchar2(100):='';
4 vRtn varchar2(100):='';
5 iTmp int:=0;
6 begin
7 vTmp := vStr;
8 while Instr(vTmp,'1')>0 loop
9 vTmp := vTmp ||','|| (Instr(vTmp,'1')+iTmp);
10 iTmp := iTmp + Instr(vTmp,'1');
11 vTmp := substr(vTmp,Instr(vTmp,'1')+1);
12 end loop;
13 return vTmp;
14 end fun_One;
15 /函数已创建。SQL> select fun_One('0010111') from dual;FUN_ONE('0010111')
------------------------------------------------------
,3,5,6,7
decode(substr(s,3),'1',',3')|| decode(substr(s,4),'1',',4')||
decode(substr(s,5),'1',',5')|| decode(substr(s,6),'1',',6'),2)
from t;
2 nullif(instr(col, '1', 1, 1), 0) || nullif(',' || instr(col, '1', 1, 2), ',0') ||
3 nullif(',' || instr(col, '1', 1, 3), ',0') || nullif(',' || instr(col, '1', 1, 4), ',0') ||
4 nullif(',' || instr(col, '1', 1, 5), ',0') || nullif(',' || instr(col, '1', 1, 6), ',0')
5 FROM (SELECT '000101' col FROM dual UNION
6 SELECT '101001' FROM dual UNION
7 select '000000' from dual UNION
8 select '111111' from dual)
9 /COL NULLIF(INSTR(COL,'1',1,1),0)||
------ --------------------------------------------------------------------------------
000000
000101 4,6
101001 1,3,6
111111 1,2,3,4,5,6