SQL advanced -> Using Tally table to split sentence to words

How we can split sentence to words without use of loops and other programming tools ? We have table EmployeeFacts that stores info about employees written in sentences.

FactNofact
1maggi wants to go to vacation in September this year
2in April voich was late to work twice
3aiva was a best sales person for year 1999. she will ...

Now we create tally table and fill it with numbers from 1 to 255.
-- create table numseq
CREATE TABLE numseq (n INTEGER);

INSERT INTO numseq (n) VALUES (1);
INSERT INTO numseq (n) VALUES (2);
INSERT INTO numseq (n) VALUES (3);
-- and so on

In the next query every character of EmployeeFacts sentence is "scaned" by the MID() function that uses sequentual numbers from the tally numseq table. Result dataset is saved to the intermidiate table:
SELECT a.FactNo, ' ' & a.fact & ' ' AS efact, b.n
INTO efacts_intermid
FROM EmployeeFacts AS a, numseq AS b
WHERE MID(' ' & a.fact & ' ',b.n,1)=' '
AND b.n <= LEN(' ' & a.fact & ' ') ORDER BY a.FactNo, b.n;

MID() function helps us to find spaces positions that we accept as word separators in the sentence. efacts_intermid table will look like this after we'll add to it id-number field:
idnoFactNoefactn
11maggi wants to go to vacation ...1
21maggi wants to go to vacation ...7
31maggi wants to go to vacation ...13
41maggi wants to go to vacation ...16
............

After this everthing is ready to the succesfull split:
SELECT a.idno, a.FactNo, a.efact, MID(a.efact,a.n,b.n-a.n) AS word
FROM efacts_intermid AS a INNER JOIN efacts_intermid AS b
ON (a.idno=(b.idno-1)) AND (a.FactNo=b.FactNo);

In this query efacts_intermid table is joined to itself in order to retrieve two sequential N numbers every time. Result will look as follows:
idnoFactNoefactword
11maggi wants to go to vacation ...maggi
21maggi wants to go to vacation ...wants
31maggi wants to go to vacation ...to
41maggi wants to go to vacation ...go
51maggi wants to go to vacation ...to
61maggi wants to go to vacation ...vacation
71maggi wants to go to vacation ...in
81maggi wants to go to vacation ...September
91maggi wants to go to vacation ...this



sqlexamples.info