Converting CLOB to NUMBER to compare - Oracle

Question

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

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

My attempts:

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?

Thanks for your time and help.

Solution 1

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

WHERE TRIM(clob_field) = '100'

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

WHERE clob_field = '100'

Solution 2

Cast it first to a VARCHAR2 and then to a NUMBER:

CAST(CAST(clob_field AS VARCHAR2(200)) AS NUMBER(10))