Search problem

[Manish]

Hi
How do i search for text in attachments?
All the file attachments are stored in a blob in a table.

What sql will work to search for text in those attachments?
I tried using 'CONTAINS'..but it is ot working.

TIA

manish

[Roland Smith [TeamSybase]]

This is not possible with standard SQL statements. The closest would be LIKE
'%mytest%' but the datatype would have to be a text type.

There might be an add-in for your database. What is your database?

"Manish" <m...@hotmail.com> wrote in message
news:10teknhlqpoev$.le46rdwf51jb.dlg@40tude.net...

[Manish]

the DB is oracle 10g

[Bruce Armstrong [TeamSybase]]

Oracle has a full text search query option.

"Manish" <m...@hotmail.com> wrote in message

news:1ao68qm7fnzgc$.1lfpfraxwgs0e.dlg@40tude.net...

[Terry Dykstra]

That option depends on your version of Oracle. Standard Edition does not
have that.
You can use the DBMS_LOB.INSTR() function to search for text within a blob.

--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_br...@teamsybase.com>
wrote in message news:48b34cee$1@forums-1-dub...

[Manish]

On 26 Aug 2008 10:23:17 -0700, Terry Dykstra wrote:

> That option depends on your version of Oracle. Standard Edition does not
> have that.
> You can use the DBMS_LOB.INSTR() function to search for text within a blob.

Thanks for the help.
How do i use it?Can you provide an example?

[Roland Smith [TeamSybase]]

I did a Google search and found this:

http://www.psoug.org/reference/dbms_lob.html

"Manish" <m...@hotmail.com> wrote in message

news:1p9ias0h9jxq7.j...@40tude.net...

[Roland Smith [TeamSybase]]

This should work

where dbms_lob.instr(clob_column, 'the string') > 0