Words from one column to different columns

[muumigirl]

In one column there is text that consists of three words and two spaces. I need to put the first two words into one column and the last one into another column. The words are always of different length
For example:
Field1_____________|Field2_____|Field3
SAUE VALD HARJUMAA |SAUE VALD |HARJUMAA

And accordingly the address should be put to different columns
Field1___________|Field2____|Field3__|Field4
PURU TEE 20A-32 |PURU TEE |20A |32
SAARE 1 |SAARE |1 |

 

[PHV]

A starting point:
SELECT [yourField] AS Field1, Left([yourField],InStrRev(RTrim([yourField])," ")-1) AS Field2, Mid([yourField],InStrRev(RTrim([yourField])," ")+1) AS Field3
FROM [yourTable]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eupher]

You might also take a look at the Split function.

Ken S.