char类型 是数字才能转换成Number 不然肯定会报错invalid number 看看数据库里面有不是数字的a么
用translet函数查了一下,有记录的a字段,包含'0123456789.'之外的字符,然后加了过滤条件where translet(a,'~0123456789.'~') is null,这样执行能通过了,不知道有没有好方法过滤a字段包含数字之外的字符的记录
with t1 as ( select '123' c1 from dual union all select '456.' from dual union all select '0123' from dual union all select '12.33' from dual ) select * from t1 where regexp_like(c1,'^[0-9]+([.]{0}|[.]{1}[0-9]+)$') c1 ------------------ 1 123 2 0123 3 12.33
with t1 as
(
select '123' c1 from dual
union all
select '456.' from dual
union all
select '0123' from dual
union all
select '12.33' from dual
)
select *
from t1
where regexp_like(c1,'^[0-9]+([.]{0}|[.]{1}[0-9]+)$') c1
------------------
1 123
2 0123
3 12.33