ops$lg\[email protected] >select * from t;V
--------------------
124315
afasf4315
afasf4315afasops$lg\[email protected] >select v from t where nvl(TRANSLATE(v,'0123456789','0'),1)<>'1';V
--------------------
afasf4315
afasf4315afas
--------------------
124315
afasf4315
afasf4315afasops$lg\[email protected] >select v from t where nvl(TRANSLATE(v,'0123456789','0'),1)<>'1';V
--------------------
afasf4315
afasf4315afas
我来一个挺一般的方法:
SQL> ed
已写入文件 afiedt.buf 1 create or replace function get_non_int(str in varchar2)
2 return varchar2
3 is
4 i int:=1;
5 first varchar2(100);
6 str_some varchar2(100);
7 result varchar2(100):='';
8 begin
9 while i<=length(str)
10 loop
11 str_some:=substr(str,i);
12 first :=substr(str_some,1,1);
13 if ascii(first) not between 48 and 57
14 then
15 result:= result||first;
16 end if;
17 i:=i+1;
18 end loop;
19 return (result);
20* end;
SQL> /函数已创建。SQL> select get_non_int('345g') from dual;GET_NON_INT('345G')
--------------------------------------------------------------------------------
g
谢谢 过讲!快到一星了吧。
----------
123456789
123456789A
ASDFASDFSA
12346.5645
123465645.
.123465645已选择6行。SQL> SELECT * FROM TEST WHERE TRIM(TRANSLATE(A, '.0123456789', LPAD(' ',11))) IS NULL;A
----------
123456789
12346.5645
123465645.
.123465645