问题描述
我想使用to_number函数将varchar列转换为数字,但是在理解Oracle尝试执行SQL的顺序时遇到了一些麻烦.
I want to convert a varchar column into a number using the to_number function however I have some trouble understanding the order in which Oracle attempts to execute my SQL.
语句看起来像这样;
select * from table where column is not null and to_number(column, '999.9') > 20
Oracle执行此操作时,将引发无效的数字异常.我知道Oracle使用某种关系代数公式优化了SQL语句,但是有人可以告诉我如何安全地使用to_number运算符来实现我的目标吗?
When Oracle executes this it throws an invalid number exception. I understand that Oracle optimized the SQL statement using some kind of relational algebraic formula however can someone tell me how I can safely use the to_number operator to achieve my goal?
推荐答案
不幸的是,您必须,然后再应用to_number
.转换函数本身是不安全的",如果您愿意的话,它将使整个查询在单个无效输入上崩溃.
Unfortunately, you'll have to first filter out the rows with non-numerical data somehow before you apply to_number
. The conversion function itself is "not safe", if you will, it will crash the whole query on a single invalid input.
这篇关于Oracle-将to_number函数应用于varchar列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!