oracle 判断是否是数字-不用捕捉异常

字段work_years 的值如果是数字则返回 work_years,如果不是数字则返回'0'

case when lpad(translate(work_years,'123456789','000000000'),10,'0')='0000000000' then work_years else '0' end as n2

如果是oracle11g，可以这样使用 regexp_Like(字符串,'^[0-9]+$') :

select * from (
select 'abc' s1 from dual 
union all select '789456' s1 from dual
union all select '8907a7' s1 from dual
union all select '3' s1 from dual
union all select '' s1 from dual
union all select null s1 from dual
) where regexp_Like(s1,'^[0-9]+$')
;