sql server - T-SQL: Separate String Into Multiple Columns

Question

Ex.

Column 1:
| word1 word2 word3 word4 |

to

 Col 1:  Col 2:  Col 3:  Col 4:
| word1 | word2 | word3 | word |

Is it possible to separate different words or phrases from a string into multiple columns? All words and phrases in the strings are usually separated by double spaces, nothing else. Is there a pre-defined function I can use already available from SQL Server like CAST or INTERSECT, or do I have to write my own?

Answer 1

With the help of a CROSS APPLY and some XML. Easy to expand and/or contract

Declare @YourTable table (id int,Column1 varchar(max))
Insert Into @YourTable values
(1,'word1 word2 word3 word4'),
(2,'some other words')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
        Select Pos1 = xDim.value('/x[1]','varchar(max)')
              ,Pos2 = xDim.value('/x[2]','varchar(max)')
              ,Pos3 = xDim.value('/x[3]','varchar(max)')
              ,Pos4 = xDim.value('/x[4]','varchar(max)')
              ,Pos5 = xDim.value('/x[5]','varchar(max)')
              ,Pos6 = xDim.value('/x[6]','varchar(max)')
              ,Pos7 = xDim.value('/x[7]','varchar(max)')
              ,Pos8 = xDim.value('/x[8]','varchar(max)')
              ,Pos9 = xDim.value('/x[9]','varchar(max)')
         From (Select Cast('<x>' + Replace(A.Column1,' ','</x><x>')+'</x>' as XML) as xDim) A
       ) B

Returns