compare clob and varchar2

[edli]

hi,

I have to make a query which has in the where clause a condition which is that a certain string is included in the clob field.
Is there any function which does this or no?

select *
from t1
where t1.clobfield like '%string_var%';

thanks

[TomazZ]

dbms_lob?

[edli]

well I know there is dbms_lob.compare but its for two lobs and haven't found one whitch tests if a varchar2 is part of the lob or not? I you know it,that would be very helpfull.

thanks

[tombest]

The function DBMS_LOB.INSTR can be used to see if a string occurs in a LOB.

See this:

http://download-east.oracle.com/docs...b2.htm#1008998

Also, you could use Oracle Text, or (Intermedia in 8i) and index the tokens of the clob.