Oracle-将to_number函数应用于varchar列 [英] Oracle - applying the to_number function to a varchar column

问题描述

我想使用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运算符实现我的目标吗?

can someone tell me how I can safely use the to_number operator to achieve my goal?

不幸的是，您必须

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列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！