ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 判断一个字符串中的数字,并将其挑选出来

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

原创 Linux操作系统 作者:howard_zhang 时间:2011-03-22 21:55:58 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-22

  • 博文量
    42
  • 访问量
    133148