Re: Queen/Pink Floyd

From: Monty <mmontreaux_at_hotmail.com>
Date: 16 Dec 2002 02:29:05 -0800
Message-ID: <6284dd3.0212160229.79b13a33@posting.google.com>

DA Morgan <damorgan_at_exesolutions.com> wrote in message

Dan, just enough for me to almost hang myself. I'm posting my solution here not for criticism but to save someone else the large amount of time it took me to write. And I know it shouldn't have taken me long. Sad.

Monty

SQL> CREATE TABLE tbltest(a VARCHAR2(200));

Table created.

SQL> INSERT INTO tbltest(a)
2 VALUES ('brian may john deacon roger taylor freddy mercury');

1 row created.

SQL> INSERT INTO tbltest(a)
2 VALUES ('nick mason david gilmour roger waters richard wright');

1 row created.

SQL>
SQL> CREATE TABLE tokenised(b VARCHAR2(25));

Table created.

SQL>
SQL> DECLARE
  2 CURSOR ctest IS SELECT * FROM tbltest;   3 ctestrow tbltest%ROWTYPE;
  4
  5 spacepositionoffset NUMBER;
  6 spaceposition NUMBER;
  7
  8 BEGIN
  9 OPEN ctest;
10 LOOP
11 FETCH ctest INTO ctestrow;
12 EXIT WHEN ctest%NOTFOUND;
13
14 spacepositionoffset:=1;
15 LOOP

 16     spaceposition:=INSTR(ctestrow.a,' ',spacepositionoffset);
 17     IF spaceposition=0 THEN
 18            INSERT INTO tokenised(b) VALUES
(SUBSTR(ctestrow.a,spacepositionoffset,LENGTH(ctestrow.a)-spacepositionoffset+1));
 19      ELSE
 20            INSERT INTO tokenised(b) VALUES
(SUBSTR(ctestrow.a,spacepositionoffset,spaceposition-spacepositionoffset));
 21     END IF;
 22  
 23     spacepositionoffset:=spaceposition+1;
 24     EXIT WHEN spaceposition=0;

25 END LOOP;
26
27 END LOOP;
28 CLOSE ctest;
29 END;
30 /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> SELECT b FROM tokenised;

brian
may
john
deacon
roger
taylor
freddy
mercury
nick
mason
david

gilmour
roger
waters
richard
wright

16 rows selected.

SQL>
> news:<3DFA6F21.A7AE713C_at_exesolutions.com>...
> Monty wrote:
>
> > Hi, don't ask why but ....
> >
> > CREATE TABLE tbltest(a VARCHAR2(200));
> > INSERT INTO tbltest(a) VALUES ('brian may john deacon roger taylor
> > freddy mercury');
> > INSERT INTO tbltest(a) VALUES ('nick mason david gilmour roger waters
> > richard wright');
> >
> > Is it a simple exercise to create another table from this table, so
> > that:
> >
> > SQL >
> > SQL > SELECT * FROM rock;
> > brian
> > may
> > john
> > deacon
> > roger
> > taylor
> > freddy
> > mercury
> > nick
> > mason
> > david
> > gilmour
> > roger
> > waters
> > richard
> > wright
> >
> > SQL>
> > SQL>
>
> Very simple.
>
> You will need:
> 1. An anonymous block
> 2. A cursor
> 3. A loop
> 4. INSTR
> 5. SUBSTR
> 6. An insert statement
> 7. A commit
>
> Dan Morgan
Received on Mon Dec 16 2002 - 04:29:05 CST