---根据mssql isnumeric改进判断是否为正整数 1为正整数,0为数字,字符等 create or replace function f_isnumeric(cnt varchar2) return number as res number; flag number; begin if cnt is null then return 0; end if; select to_number(cnt) into res from dual; select decode(instr(res,'.')+sign(res),1,1,0) into flag from dual; if flag=1 then return 1; else return 0; end if; exception when others then return 0; end; SQL> create or replace function f_isnumeric(cnt varchar2) return number 2 as 3 res number; 4 flag number; 5 begin 6 if cnt is null then 7 return 0; 8 end if; 9 select to_number(cnt) into res from dual; 10 select decode(instr(res,'.')+sign(res),1,1,0) into flag from dual; 11 if flag=1 then 12 return 1; 13 else 14 return 0; 15 end if; 16 exception 17 when others then 18 return 0; 19 end; 20 /
Function created
SQL> select f_isnumeric('s'),f_isnumeric(10),f_isnumeric(1.2) from dual / 2 /
--也可以这样with tb as (select '1' res from dual union all select '13' res from dual union all select 's2' from dual union all select '1.2' from dual union all select '稳靠0.2' from dual union all select '0.2' from dual union all select '-10' from dual) select res from tb where length(res)=length(regexp_replace(res,'[[:alpha:]]','')) and decode(instr(res,'.')+sign(regexp_replace(res,'[[:alpha:]]','')),1,1,0)=1
SQL> SQL> with tb as 2 (select '1' res from dual union all 3 select '13' res from dual union all 4 select 's2' from dual union all 5 select '1.2' from dual union all 6 select '稳靠0.2' from dual union all 7 select '0.2' from dual union all 8 select '-10' from dual) 9 select res from tb where length(res)=length(regexp_replace(res,'[[:alpha:]]','')) 10 and decode(instr(res,'.')+sign(regexp_replace(res,'[[:alpha:]]','')),1,1,0)=1 11 /
---根据mssql isnumeric改进判断是否为正整数 1为正整数,0为数字,字符等
create or replace function f_isnumeric(cnt varchar2) return number
as
res number;
flag number;
begin
if cnt is null then
return 0;
end if;
select to_number(cnt) into res from dual;
select decode(instr(res,'.')+sign(res),1,1,0) into flag from dual;
if flag=1 then
return 1;
else
return 0;
end if;
exception
when others then
return 0;
end;
SQL> create or replace function f_isnumeric(cnt varchar2) return number
2 as
3 res number;
4 flag number;
5 begin
6 if cnt is null then
7 return 0;
8 end if;
9 select to_number(cnt) into res from dual;
10 select decode(instr(res,'.')+sign(res),1,1,0) into flag from dual;
11 if flag=1 then
12 return 1;
13 else
14 return 0;
15 end if;
16 exception
17 when others then
18 return 0;
19 end;
20 /
Function created
SQL> select f_isnumeric('s'),f_isnumeric(10),f_isnumeric(1.2) from dual
/
2 /
F_ISNUMERIC('S') F_ISNUMERIC(10) F_ISNUMERIC(1.2)
---------------- --------------- ----------------
0 1 0
SQL> select f_isnumeric('s'),f_isnumeric(10),f_isnumeric(1.2) from dual 2 /
F_ISNUMERIC('S') F_ISNUMERIC(10) F_ISNUMERIC(1.2)
---------------- --------------- ----------------
0 1 0where F_ISNUMERIC(col)=1
--也可以这样with tb as
(select '1' res from dual union all
select '13' res from dual union all
select 's2' from dual union all
select '1.2' from dual union all
select '稳靠0.2' from dual union all
select '0.2' from dual union all
select '-10' from dual)
select res from tb where length(res)=length(regexp_replace(res,'[[:alpha:]]',''))
and decode(instr(res,'.')+sign(regexp_replace(res,'[[:alpha:]]','')),1,1,0)=1
SQL>
SQL> with tb as
2 (select '1' res from dual union all
3 select '13' res from dual union all
4 select 's2' from dual union all
5 select '1.2' from dual union all
6 select '稳靠0.2' from dual union all
7 select '0.2' from dual union all
8 select '-10' from dual)
9 select res from tb where length(res)=length(regexp_replace(res,'[[:alpha:]]',''))
10 and decode(instr(res,'.')+sign(regexp_replace(res,'[[:alpha:]]','')),1,1,0)=1
11 /
RES
-------
1
13
SQL>