判断一个字符串中的数字，并将其挑选出来

create or replace procedure p_split_number
as
  i number;
  x number;
  v_sid number;
  v_con varchar2(2000);
  v_let varchar2(2);
  v_numl varchar2(100);
  cursor c1 is select id,cont from testn;
begin
  x:=1;
  i:=1;
  open c1;
  loop
    fetch c1 into v_sid,v_con;
    exit when c1%notfound;
    for i in 1 .. length(v_con) loop
      select substr(v_con,x,1) into v_let from dual;
      if (ascii(v_let) >=48 and ascii(v_let) <=57) then
        v_numl :=v_numl||v_let;
        x :=i+1;
      else
        if length(trim(v_numl))>0 then
          insert into testn1 select v_sid,v_numl from dual;
        end if;
        x :=i+1;
        v_numl :='';
      end if;
    end loop;
  end loop;
  commit;
  close c1;
end;

这里需要注意的是ascii('0') = 48, ascii('9') = '57'

SQL> select * from testn;

        ID CONT
---------- --------------------------------------------------
         1 fdsfs1213fdsfdsfdsa334343fdsfds

SQL> select * from testn1;

        ID CONT
---------- --------------------------------------------------
         1 1213
         1 334343

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7882490/viewspace-690199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7882490/viewspace-690199/