Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Text searching within multiple rows

Re: Text searching within multiple rows

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: Thu, 5 Dec 2002 09:26:38 -0500
Message-ID: <3def5f52$1_8@news.teranews.com>


I think you'd have to do some kind of self-join if you did it in SQL, and if it had variable numbers of rows, it would be impossible. Does something like this capture what you want?

SQL> create table t (line_num number,text varchar2(80));

Table created.

SQL>
SQL> insert into t (line_num,text ) values (1,'This is line 1');

1 row created.

SQL>
SQL> insert into t (line_num,text ) values (2,'This is line 2 how now brown cow');

1 row created.

SQL>
SQL> insert into t (line_num,text ) values (3,'This is line 3 four score and 7 years ago');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> create or replace procedure stitch

  2 as
  3 l_textall varchar2(4000);
  4 begin
  5     for x in ( select text from t order by line_num )
  6     loop
  7        l_textall := l_textall || ' ' || x.text;
  8     end loop;
  9     dbms_output.put_line( l_textall );
 10     -- do regex here

 11 end;
 12 /

Procedure created.

SQL>
SQL>
SQL> exec stitch;

This is line 1 This is line 2 how now brown cow This is line 3 four score and 7 years ago

PL/SQL procedure successfully completed. Received on Thu Dec 05 2002 - 08:26:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US