like comparision on a CLOB?

[MattWoberts]

Hi,

I have a CLOB column in my table, and I want to do a like comparison, like this:

Select * from MyTable where Clob_Column like '%HELLO%';

But I can't do that in Oracle? Is there a workaround?
Thanks!

 

[SantaMufasa]

Matt (BTW, are you "welated to the dwead piwat Woberts?"),

In Oracle 9i versions, Oracle treats CLOBs just like VARCHAR2s. If there is any way that you can get that schema (or even the table) into an Oracle 9i instance, you can successfully use "LIKE" operations.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:26 (29Sep04) UTC (aka "GMT" and "Zulu"), 09:26 (29Sep04) Mountain Time)

 

[Custom24]

Hi
If you are stuck on 8i, then you can try my function

CREATE OR REPLACE function ClobIsLike (TheClob in Clob, LikeString in varchar2) return number
as
returnValue number;
LengthOfClob integer;
varRepresentation varchar2(32767);
begin
LengthOfClob := dbms_lob.getlength(TheClob);
varRepresentation := dbms_lob.substr(TheClob, LengthOfClob, 1);
returnValue := 0; --false
if varRepresentation like LikeString then
returnValue := 1;
end if;
return returnValue;
end;
/

It could be improved on, but it just proves that it works. The max length of the Clob it will accept is 32767, because that is the max length of a varchar2 in PL/SQL, and I don't know of another way than converting to varchar2.

To use the function from SQL;

select * from Test_Clob t where ClobIsLike(t.MYCLOB, '%Mark') = 1;

Hope this helps.

Mark

 

[sem]

Select * from MyTable where DBMS_LOB.INSTR(Clob_Column, '%HELLO%')>0

Regards, Dima

http://seminihin.narod.ru

 

[MattWoberts]

Thanks everyone for the helpful replies. I had no idea oracle9i trated CLOBs as VARCHARs!

Anyway, for the time being, DBMS_LOB.INSTR is doing the job well, so I will go with that!!

Thanks again

 

[SantaMufasa]

Matt,

Normally, I'd suggest awarding to Sem/Dima a Purple Star ("Thank sem for this valuable post!") since his post was so helpful and he got you out of a jam, but I won't since Sem doesn't like my doing that.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:55 (30Sep04) UTC (aka "GMT" and "Zulu"), 08:55 (30Sep04) Mountain Time)