|
|
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.
| FactNo | fact |
| 1 | maggi wants to go to vacation in September this year |
| 2 | in April voich was late to work twice |
| 3 | aiva 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:
| idno | FactNo | efact | n |
| 1 | 1 | maggi wants to go to vacation ... | 1 |
| 2 | 1 | maggi wants to go to vacation ... | 7 |
| 3 | 1 | maggi wants to go to vacation ... | 13 |
| 4 | 1 | maggi 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:
| idno | FactNo | efact | word |
| 1 | 1 | maggi wants to go to vacation ... | maggi |
| 2 | 1 | maggi wants to go to vacation ... | wants |
| 3 | 1 | maggi wants to go to vacation ... | to |
| 4 | 1 | maggi wants to go to vacation ... | go |
| 5 | 1 | maggi wants to go to vacation ... | to |
| 6 | 1 | maggi wants to go to vacation ... | vacation |
| 7 | 1 | maggi wants to go to vacation ... | in |
| 8 | 1 | maggi wants to go to vacation ... | September |
| 9 | 1 | maggi wants to go to vacation ... | this |
|