DA Morgan <damorgan@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(ctes trow.a)-spacepositionoffset+1));
19 ELSE
20 INSERT INTO tokenised(b) VALUES
(SUBSTR(ctestrow.a,spacepositionoffset,spacepositi on-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@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

Monty wrote:

> DA Morgan <damorgan@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(ctes trow.a)-spacepositionoffset+1));
> 19 ELSE
> 20 INSERT INTO tokenised(b) VALUES
> (SUBSTR(ctestrow.a,spacepositionoffset,spacepositi on-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@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