Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: select like %xyz% in a CLOB???

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

From: Rom1 <rm2_at_fr.st>
Date: Fri, 11 Jan 2002 21:31:44 +0100
Message-ID: <3c3f4bce$0$3180$626a54ce@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:31:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US