Break address field into separate fields by ' '

Hi All,

I have an existing SQL statement I am using to extract a bunch of data. At the current time, the address1 field can contain any combination of apt #, street number, street name, and other misc items - very inconsistent.

I have an address1 field.....for example:

21-410 Anywhere St Floor 3

I need to separate this field (by space) into the following 5 separate fields like the following.



21-410
Anywhere
St
Floor
3

Does anyone have an example of code for this - Would it be the instr function (at least to start)?

I really appreciate the help!