将CLOB转换为NUMBER以进行比较-Oracle [英] Converting CLOB to NUMBER to compare - Oracle

问题描述

我是oracle的新手，我的问题很简单，可以将CLOB字段转换为NUMBER类型以进行比较。

I'm newbie to oracle and my question is easy, is possible to convert a CLOB field into NUMBER type to do a comparison.

我尝试使用CAST以及TO_NUMBER函数，但我什么也没做。

I´ve tried using CAST and also with TO_NUMBER function but i did not get nothing working.

我的尝试：

WHERE TO_NUMBER(clob_field) = 100 -> Error ORA-01722: NOT a valid number

WHERE CAST(clob_field as NUMBER) = 100 -> Error ORA-00932:Inconsistent datatypes

如果这不可能，那么这将是最好的方法

If this is not possible, which would be the best approach to do this?

感谢您的时间和帮助。

推荐答案

比较将其作为varchar可以消除错误消息：

Compare it as varchar to get rid of the error message:

WHERE TRIM(clob_field) = '100'

如果 100 周围没有空格，则可以跳过 TRIM ：

If there is never any whitespace around the 100 you can skip the TRIM:

WHERE clob_field = '100'

这篇关于将CLOB转换为NUMBER以进行比较-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！