一种方法就是采用ORACLE 10G的正则表达式 select * from emp where REGEXP_LIKE(empno,’^[:punct:]|[0-9]+$’)
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as billing---大于0代表是非法的数字,等于0代表是数字字符 SQL> select regexp_instr('121212adfazxczxc111111sdfasdsadf','[^[:digit:]]') from dual;REGEXP_INSTR('121212ADFAZXCZXC ------------------------------ 7SQL> select regexp_instr('1111111111232323','[^[:digit:]]') from dual;REGEXP_INSTR('1111111111232323 ------------------------------ 0SQL> select regexp_instr('aaaaa1111111111232323','[^[:digit:]]') from dual;REGEXP_INSTR('AAAAA11111111112 ------------------------------ 1SQL>
我的ORACLE版本是8.1.7.0.1 的,好象用不了
写个函数 使用to_number转换,如遇异常则有非数字,没有则没有
用decode函数,不过:不知道ORACLE版本是8.1.7.0.1中是否有这个函数!
用个简单的函数吧..SQL> create or replace function checkNumber(numStr in varchar) 2 return number 3 as 4 o_ret number; 5 num number; 6 begin 7 select to_number(numStr) into o_ret from dual; 8 o_ret:=0; 9 return o_ret; 10 exception 11 when others then 12 o_ret:=1; 13 return o_ret; 14 end; 15 /Function createdSQL> select decode(checkNumber('1231'),0,'数字','非法数字') from dual;DECODE(CHECKNUMBER('1231'),0,' ------------------------------ 数字SQL> select decode(checkNumber('123err1'),0,'数字','非法数字') from dual;DECODE(CHECKNUMBER('123ERR1'), ------------------------------ 非法数字SQL>
select * from emp where REGEXP_LIKE(empno,’^[:punct:]|[0-9]+$’)
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as billing---大于0代表是非法的数字,等于0代表是数字字符
SQL> select regexp_instr('121212adfazxczxc111111sdfasdsadf','[^[:digit:]]') from dual;REGEXP_INSTR('121212ADFAZXCZXC
------------------------------
7SQL> select regexp_instr('1111111111232323','[^[:digit:]]') from dual;REGEXP_INSTR('1111111111232323
------------------------------
0SQL> select regexp_instr('aaaaa1111111111232323','[^[:digit:]]') from dual;REGEXP_INSTR('AAAAA11111111112
------------------------------
1SQL>
使用to_number转换,如遇异常则有非数字,没有则没有
2 return number
3 as
4 o_ret number;
5 num number;
6 begin
7 select to_number(numStr) into o_ret from dual;
8 o_ret:=0;
9 return o_ret;
10 exception
11 when others then
12 o_ret:=1;
13 return o_ret;
14 end;
15 /Function createdSQL> select decode(checkNumber('1231'),0,'数字','非法数字') from dual;DECODE(CHECKNUMBER('1231'),0,'
------------------------------
数字SQL> select decode(checkNumber('123err1'),0,'数字','非法数字') from dual;DECODE(CHECKNUMBER('123ERR1'),
------------------------------
非法数字SQL>