How to compare Clob column for multiple values?

Question

Can anyone tell me how to compare column which has clob datatype in oracle for multiple values?

For one value we are comparing like
dbms_lob.compare(attr_value,'A')=0

Similarly if I want to know whether attr_value is in ('A','B','C','D'). I tried this:

dbms_lob.compare(attr_value,'A')=0 or  dbms_lob.compare(attr_value,'B')=0 or ...

This is not giving me proper result. Is there any other way?

Answer 1

Since it seems you don't really want to compare CLOBS of massive size with a bunch of other massive CLOBS, the fastest way would be to just compare a Substring of the CLOB:

WHERE DBMS_LOB.SUBSTR( attr_value, 4000, 1 ) IN ('A','B','C')

Here 4000 can be replaced by the maximum length of all you comparison values.

If you really want to compare massive CLOBS I don't think a select is the right approach, you should probably rework your application logic...

Answer 2

OR should work fine. Also you may try this:

SELECT * FROM your_tab WHERE CAST(s as VARCHAR2(2)) IN ('A', 'B', 'C', 'D');

but I'm not sure about the performance.

Answer 3

DBMS_LOB.COMPARE does an exact comparison between two LOB objects. The documentation says:

COMPARE returns zero if the data exactly matches over the range specified by the offset and amount parameters. Otherwise, a nonzero INTEGER is returned.

On Oracle 11g, you could use REGEXP_INSTR function:

SELECT REGEXP_INTR(attr_value,'A|B|C|D|E') from dual; 

I hope it helps.