oracle to_namber之后报ora-01722:无效数字

写的sql:

select 

 to_number(substr(B.年龄, 1, instr(B.年龄, ‘天‘) - 1)) as 年龄, A.病人id, A.主页id

  from 病案主页 A, 病人信息 B

 where A.病人ID = B.病人ID

   and A.主页ID = B.主页ID

   and to_number(substr(B.年龄, 1, instr(B.年龄, ‘天‘) - 1)) < 29

   and B.年龄 not like ‘%岁%‘

   and B.年龄 not like ‘%月%‘

报错:

加上强制规则,运行通过:

select /*+rule*/

 to_number(substr(B.年龄, 1, instr(B.年龄, ‘天‘) - 1)) as 年龄, A.病人id, A.主页id

  from 病案主页 A, 病人信息 B

 where A.病人ID = B.病人ID

   and A.主页ID = B.主页ID

   and to_number(substr(B.年龄, 1, instr(B.年龄, ‘天‘) - 1)) < 29

   and B.年龄 not like ‘%岁%‘

   and B.年龄 not like ‘%月%‘

注意:

and to_number(substr(B.年龄, 1, instr(B.年龄, ‘天‘) - 1)) < 29

不能放在最后一个条件,否则也会报错！

本文出自 “菜鸟先飞” 博客，谢绝转载！

原文：http://1826794.blog.51cto.com/1816794/1902500