Oracle中有字符数字,截取数字

2024-12-26 18:12:09
推荐回答(1个)
回答1:

这个最后写个split函数,然后取数组的第三个数据。
CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100);
create or replace function split(src varchar2, delimiter varchar2)
return mytable is
psrc varchar2(500);
a mytable := mytable();
i number := 1; --
j number := 1;
begin
psrc := rtrim(ltrim(src, delimiter), delimiter);
loop
i := instr(psrc, delimiter, j);
--dbms_output.put_line(i);
if i > 0 then
a.extend;
a(a.count) := trim(substr(psrc, j, i - j));
j := i + 1;
--dbms_output.put_line(a(a.count-1));
end if;
exit when i = 0;
end loop;
if j < length(psrc) then
a.extend;
a(a.count) := trim(substr(psrc, j, length(psrc) + 1 - j));
end if;
return a;
end;