declare tmp_str nvarchar2(); Type array_test is Table of varchar2(5000) index by Binary_Integer; myarray array_test; i integer; begin tmp_str := '16:36:36~202001~123.00~失败'; i:=1; while INSTR(tmp_str,'~')>0 loop myarray(i):=subSTR(tmp_str ,1,(INSTR(tmp_str ,'~')-1)); tmp_str :=subSTR(tmp_str ,(INSTR(tmp_str ,'~')+1)); i:=i+1; end loop; myarray(i + 1) := tmp_str; end;
16:36:36~202001~123.00~失败select substr('16:36:36~202001~123.00~失败',1,instr('16:36:36~202001~123.00~失败',1,'~')-1 into a from duak 其它的类似呗
SQL> with t as( 2 select rownum,regexp_substr('16:36:36~202001~123.00~失败','[^~]+',1,level) 3 as cl1 4 from dual 5 connect by 6 level<=length('16:36:36~202001~123.00~失败')- 7 length(replace('16:36:36~202001~123.00~失败','~',''))+1 8 ) 9 select * from t; ROWNUM CL1 ---------- -------------------------------------------------- 1 16:36:36 2 202001 3 123.00 4 失败
1樓修改 declare tmp_str varchar2(200); Type array_test is Table of varchar2(5000) index by Binary_Integer; myarray array_test; i integer; begin tmp_str := '16:36:36~202001~123.00~AAA'; i:=1; while INSTR(tmp_str,'~')>0 loop myarray(i):=subSTR(tmp_str ,1,(INSTR(tmp_str ,'~')-1)); tmp_str :=subSTR(tmp_str ,(INSTR(tmp_str ,'~')+1)); i:=i+1; end loop; myarray(i) := tmp_str; dbms_output.put_line(myarray(4)); end;
declare
tmp_str nvarchar2();
Type array_test is Table of varchar2(5000) index by Binary_Integer;
myarray array_test;
i integer;
begin
tmp_str := '16:36:36~202001~123.00~失败';
i:=1; while INSTR(tmp_str,'~')>0 loop
myarray(i):=subSTR(tmp_str ,1,(INSTR(tmp_str ,'~')-1));
tmp_str :=subSTR(tmp_str ,(INSTR(tmp_str ,'~')+1));
i:=i+1;
end loop;
myarray(i + 1) := tmp_str;
end;
其它的类似呗
SQL> with t as(
2 select rownum,regexp_substr('16:36:36~202001~123.00~失败','[^~]+',1,level)
3 as cl1
4 from dual
5 connect by
6 level<=length('16:36:36~202001~123.00~失败')-
7 length(replace('16:36:36~202001~123.00~失败','~',''))+1
8 )
9 select * from t; ROWNUM CL1
---------- --------------------------------------------------
1 16:36:36
2 202001
3 123.00
4 失败
declare
tmp_str varchar2(200);
Type array_test is Table of varchar2(5000) index by Binary_Integer;
myarray array_test;
i integer;
begin
tmp_str := '16:36:36~202001~123.00~AAA';
i:=1; while INSTR(tmp_str,'~')>0 loop
myarray(i):=subSTR(tmp_str ,1,(INSTR(tmp_str ,'~')-1));
tmp_str :=subSTR(tmp_str ,(INSTR(tmp_str ,'~')+1));
i:=i+1;
end loop;
myarray(i) := tmp_str;
dbms_output.put_line(myarray(4));
end;