Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: select like %xyz% in a CLOB???
"Andre Vaupel" <avaupel_at_web.de> a écrit dans le message de news:
K3H%7.380062$tK5.25726_at_news.easynews.com...
> Hi!
> I would like to do this:
> select * from table where longtext like '%hello%';
>
> In a vchar2 field it works perfect...in a "long" or
> clob filed it dosen´t. How can i do that?
>
> I spent many many hours, but could´nt find a good solution.
>
> How can i do this....i read something about indexes and Intermedia...
>
> but is this the only (and easy) way?
>
> Thanks for any help!!!!
>
>
> Andre
>
>
>
Hello,
One trick is to query the table by using the DBMS_LOB.INSTR(<column name>,
'<string to be searched>') procedure. If the specified string is in the
CLOB column, the procedure will return the position of the first appearance
of the string. So if the output value for the procedure is >0, the string
exist in the CLOB column.
SQL> select text from wwv_text where dbms_lob.instr(text, 'marathon')>0;
Hope this helps Received on Fri Jan 11 2002 - 14:31:44 CST