Re: SQL Query- filter on text in CLOB

On 23 Aug 2004 11:31:33 -0700, bbcrock_at_hotmail.com (D) wrote:

>How can I writ the simplest query to select all the IDs from a table
>where a certain string is present in a CLOB field?
>
>I want to do a LIKE comparison, but that doesn't work. I looked at
>dbms_lob.instr and dbms_lob.substr but neither seem to work right.

 They do "work right". What went wrong when you tried?

> I tried to_char in the where clause, but I probably have the syntax
>wrong.

 Not going to work for a CLOB past 4000 characters, since that's the limit for a VARCHAR2 produced by TO_CHAR() in an SQL statement.

>If anyone can point me to the absolute fastest way to write this query
>let me know.

 Which do you want, fastest or simplest? Oracle Text becomes an option if you want fastest.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool