Trying to find a value in a Clob using SQL

Question

I'm trying to create a column in a SQl statement that will show me if a Clob, which contains XML, has a set value in it.

I have tried the following but it does not appear to work:

NVL2(dbms_lob.substr(XML_DATA, length('<MirrorId></MirrorId>'), dbms_lob.instr(XML_DATA,'<MirrorId></MirrorId>')),'Found','Not Found') as TestColumn

When I run this code it always appears to come back as not found when there is data.

Any advice?

Michel

Answer 1

Because your post is not 100% clear, I'll make some assumptions based on your attempted query:

• xml_data column is never null.
• xml_data column never has an empty clob
• If the clob contains exactly <MirrorId></MirrorId>, then you consider that to be 'Not Found', anything else should return 'Found'.

If these assumptions are correct, then the following query should work for you:

SELECT CASE WHEN dbms_lob.instr(xml_data, '<MirrorId></MirrorId>') <> 0
            THEN 'Not Found'
            ELSE 'Found'
       END AS TestColumn
FROM <your_table_name>;