高分奉送。急！！！

select col from tab1 where f_is_number( col1 ) = 0;function f_is_number (i_col varchar2) return number
is
  ll_ret number;
begin
  ll_ret := to_number(i_col);
  return 1;
exception
  when others
    return 0;
end;

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

create or replace function test_testaa(p_in in varchar2) return varchar2 as
    v_in varchar2(20);
    ilen number(5);
    v_temp varchar2(5);
    p_out number(1);
begin
    v_in:=p_in;
    ilen:=length(v_in);
    for i in 1..ilen loop
        v_temp:=substr(v_in,i,1);
        if (ascii(v_temp)>=48 and ascii(v_temp)<=57) then
            p_out:=0;
        else
            p_out:=1;
        end if;
        if p_out=1 then
            return '²»ºÏÒªÇóµÄÊýÖµ';
            exit;
        end if;
    end loop;
    return v_in;
end;
--测试:SQL> select test_testaa('3333') from dual;TEST_TESTAA('3333')
--------------------------------------------------------------------------------
3333SQL> select test_testaa('224a') from dual;TEST_TESTAA('224A')
--------------------------------------------------------------------------------
²»ºÏÒªÇóµÄÊýÖµSQL> select test_testaa('22a2') from dual;TEST_TESTAA('22A2')
--------------------------------------------------------------------------------
²»ºÏÒªÇóµÄÊýÖµSQL> select * from testa;         ID NAME
----------- --------------------------------------------------
          5 kui
          3 dinya
          9 77
          3 dinya
         -1 ANONYMOUS
          1 AUTOINSTALL
          4 CONCURRENT MANAGER
          3 FEEDER SYSTEM
          6 GUEST
          2 INITIAL SETUP
          0 SYSADMIN
          7 WIZARD
          5 APPSMGR13 rows selectedSQL> select * from testa a where a.name=test_testaa(a.name);         ID NAME
----------- --------------------------------------------------
          9 77SQL>
--sorry ,英文系统复制过来有乱码.
SQL> select test_testaa('112a') from dual;TEST_TESTAA('112A')
--------------------------------------------------------------------------------
不合要求的数值SQL>
create or replace function test_testaa(p_in in varchar2) return varchar2 as
    v_in varchar2(20);
    ilen number(5);
    v_temp varchar2(5);
    p_out number(1);
begin
    v_in:=p_in;
    ilen:=length(v_in);
    for i in 1..ilen loop
        v_temp:=substr(v_in,i,1);
        if (ascii(v_temp)>=48 and ascii(v_temp)<=57) then
            p_out:=0;
        else
            p_out:=1;
        end if;
        if p_out=1 then
            return '不合要求的数值';
            exit;
        end if;
    end loop;
    return v_in;
end;
jxc(GameHeart)：
你的解决方法思路很好，谢谢。dinya2003(OK) :你的方法运行太慢。
我的数据每个表都是百万条记录以上。
先把字段类型改为char，假设长度为n
select * from table where translate(field1, '0123456789', '**********')<>'***..***'--有n个*
and instr(field1, '*')>0这样查出非全数字的记录