Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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 toolReceived on Mon Aug 23 2004 - 15:11:43 CDT
![]() |
![]() |