Re: select like %xyz% in a CLOB???

Hi :-)

Thanks, i will try it and see what´s happend.... !

Andre

"Rom1" <rm2_at_fr.st> schrieb im Newsbeitrag news:3c3f4bce$0$3180$626a54ce_at_news.free.fr...
>
>
> "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:51:58 CST