Tommy Tommy - 10 months ago 54
SQL Question

Converting CLOB to NUMBER to compare - Oracle

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.

Answer Source

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'