Re: Queen/Pink Floyd

Monty wrote:

> 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;
>
> B
> -------------------------
> brian
> may
> john
> deacon
> roger
> taylor
> freddy
> mercury
> nick
> mason
> david
>
> B
> -------------------------
> 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

You seem to have slipped the noose.

To be honest I took you for a student ... my response was intended to point you in the right direction without handing you the answer.

Daniel Morgan Received on Mon Dec 16 2002 - 12:42:10 CST