Re: Would like to do .... LIKE '%fred%' on LONG column

Jason Jay Weiland <archduke_at_uclink4.berkeley.edu> wrote:

>Paul,
>
> You could pull out chunks of the LONG with the DBMS_SQL.Column_Value_Long
>procedure. Using PL/SQL, you would pull out 32K (or any smaller size) chunks,
>assign them to a VARCHAR and then look for '%fred%' within that chunk. The
>obvious downsides:
>
>1) If 'fred' is on the 32K border and then split (e.g. chunck A ends with ' fre'
>and chunk B starts with 'd '). You could solve this by doing a four searches,
>offsetting the search by 0, 1, 2, then 3.
>2) If the record is anywhere near the 2GB max size, then it would take a quite a
>long time to search through several records, let alone search through each 4
>times.
>
>If you know the records that you are searching through are less then 32K, or you
>are just looking for 'fred' in the first 32K of the record, then
>Column_Value_Long wouldn't be too horrific.
>
>If you're interested I'll post a PL/SQL procedure for you to do this.
>
>Jay!!!
>

Ok, I see what you're saying Jay - yes this would work for me, since typically the part of the long column I want to search is about the first 1000 characters.

If you have smaple code to hand, I would be very grateful if you could provide it, and spare me the time poring over the manuals for the DBMS_SQL package. If not, don't worry, at least you've pointed me in the right direction.

--
Paul Smith
Technical Manager, SDR Systems Ltd
Please change Compuserve.comz to Compuserve.com to respond via Email Received on Mon Oct 26 1998 - 08:20:55 CST