Re: Text searching within multiple rows

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 05 Dec 2002 10:19:55 +0200
Message-ID: <asn2av$2qm$1@ctb-nnrp2.saix.net>

John wrote:

> So, given a table containing
>
> PROG_ID CHUNK_NUM CHUNK
> -------------------------------------------------
>
> and a search within the CHUNKs for a specific PROG_ID, is there any SQL
> that could perform this, or should I look to PL/SQL or perhaps a view that
> does the stitching of the blocks?

Ah.. you mean that you may look for the keyword 'fubar' and it may span two rows with 'fu' being in chunk 10 and 'bar' in chunk 11?

I would think that you can use a join to "stich" chunks together.

You do no need to put all the rows' chunks together into a single chunk. For search purposes you need to put chunks 1 and 2 together and search, chuncks 2 and 3 and search, and so on.

Thus, I would try something like (do not have the time to try this out myself, but this should give you an idea of what I'm thinking):

SELECT
  t1.chunk||t2.chunk STITCHED_CHUNK

FROM  program_chunks t1,
      program_chunk2 t2
WHERE t1.prog_id = t2.prog_id

AND t1.chunk_id = (t2.chunk_id - 1)

This should provide the stitching you're looking for and enable you to do a LIKE search on the stitched chunk.

--
Billy

Received on Thu Dec 05 2002 - 02:19:55 CST