How Query CLOB Column That Contains a Given String

[YerMom]

I have a table with a CLOB column and I want to select all rows where the column contains

<?xml version="1.0" encoding="UTF-8"?><AddressInfo/>

How do I do this?

thanks.

 

[Turkbear]

Hi,
This should give you some ideas..

http://www.orafaq.com/node/895

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

YerMom,

For query purposes, CLOB should behave no differently from a VARCHAR2:

SQL> desc yermom
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                                CLOB

select * from yermom;

X
-------------------------------------------------------------------------------------------------
This is leading text. <?xml version="1.0" encoding="UTF-8"?><AddressInfo/> This is trailing text.
This is leading text. <?xml version="1.0" encoding="UTF-8"?><AddressInfo/> This is trailing text.
This is leading text. <?xml version="1.0" encoding="UTF-8"?><NameInfo/> This is trailing text.
This is leading text. <?xml version="1.0" encoding="UTF-8"?><NameInfo/> This is trailing text.

select count(*) from yermom where x like '%<?xml version="1.0" encoding="UTF-8"?><AddressInfo/>%';

  COUNT(*)
----------
         2

Let us know if you are having problems.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[YerMom]

TurkBear and Santa,

Thanks for the help. I had tried something similar to Santa's sample before posting my question and got an error. I don't remember exactly what it was -- sounded like some kind of type mismatch.

In any case, Santa's query worked fine. It was slow, but that was probably because (1) the table has over 25,000 rows and (2) I'm sure that because of the potentially large size of data in a CLOB column, oracle had to spend more time searching.