SELECT TRANSLATE('字段', '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '0123456789.') from tablename
where to_number(TRANSLATE('字段', '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '0123456789.') )>100
where to_number(TRANSLATE('字段', '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '0123456789.') )>100
select * from table1 where IsNumber(col1) and col1>100
SELECT rownum,wellno,TRANSLATE(wellno, '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ', '0123456789.') from c_well
where to_number(TRANSLATE(wellno, '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '0123456789.'))>1
and
length(wellno)=lengthb(wellno) and rownum<=860
----------
我们123wef
234们f已用时间: 00: 00: 00.16
13:38:34 SQL> select * from tt where
13:38:38 2 substr(col1,instr(translate(col1,'1234567890','0000000000'),'0'),
13:38:38 3 instr(translate(col1,'1234567890','0000000000'),'0',-1)-
13:38:38 4 instr(translate(col1,'1234567890','0000000000'),'0')+1) between
13:38:38 5 200 and 300;COL1
----------
234们f已用时间: 00: 00: 00.16
13:38:39 SQL>
假设表test,字段value
数据如下
-----------
null
1
200
50
abcd
汉字
挑
......
----------------
我想找出>100的,结果肯定是一条,就200那一条
如何能做到这一点???
----------
我们123wef
234们f
157已用时间: 00: 00: 00.16
14:05:20 SQL> select * from (
14:05:25 2 select * from tt
14:05:25 3 where trim(translate(col1,'1234567890',' ')) is null
14:05:25 4 ) t where col1>100;COL1
----------
157已用时间: 00: 00: 00.31
14:05:26 SQL>
from t
where to_number(value) > 100
and ltrim(value, '0123456789') is null;注意 to_number 函数调用放在前面,Oracle 对 Where条件执行顺序为自下而上,
所以当满足最下面一个条件满足时保证了 to_number 一定不会出错。
exception
when others then
试试