17,086
社区成员
发帖
与我相关
我的任务
分享
---根据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 0
where 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>