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

This is an expansion/variation on the answer from user7075507.

There is a simple answer using/abusing the built in PARSENAME function which splits a . delimited name into 4 parts in reverse order. The limit is only 4 parts.

There is an article explaining what the function is actually for and how to abuse it. See https://www.sqlteam.com/articles/using-the-parsename-function-to-split-delimited-data

So you need to convert your delimiter to a dot reverse the string and then unreverse to get the result.

    Declare @YourTable table (id int,Column1 varchar(max))
    Insert Into @YourTable values
    (1,'word1 word2 word3 word4'),
    (2,'some other words')
    
    
    SELECT ID
        ,REVERSE(PARSENAME(REPLACE(REVERSE(Column1), ' ', '.'), 1)) Pos1
        ,REVERSE(PARSENAME(REPLACE(REVERSE(Column1), ' ', '.'), 2)) Pos2
        ,REVERSE(PARSENAME(REPLACE(REVERSE(Column1), ' ', '.'), 3)) Pos3
        ,REVERSE(PARSENAME(REPLACE(REVERSE(Column1), ' ', '.'), 4)) Pos4
    FROM @YourTable

Returns

Answer 2

How about this?

Create Table AllData (Column0 varchar(500))
Insert Into AllData Values ('word1 word2 word3 word4')
Select parsename(replace(replace(replace([Column0],'  ',' '),'  ',' '),' ','.'), 4) [Col1],
parsename(replace(replace(replace([Column0],'  ',' '),'  ',' '),' ','.'), 3) [Col2],
parsename(replace(replace(replace([Column0],'  ',' '),'  ',' '),' ','.'), 2)  [Col3],  
parsename(replace(replace(replace([Column0],'  ',' '),'  ',' '),' ','.'), 1)   [Col4]

from AllData

Answer 3

If you word count is not fixed numbr, you can use dynamic script, it'sa sample:

IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (id int,Column1 varchar(max))
insert Into #tb values
(1,'word1 word2 word3 word'),
(2,'w1 w2 w3 w4 w5 w6')

DECLARE  @Cols NVARCHAR(max),@sql nvarchar(MAX)
DECLARE @MaxWordCount INT 
SELECT @MaxWordCount=MAX(LEN(t.Column1)-len(replace(t.Column1,' ',''))+1) from #tb as t
    SELECT @Cols=ISNULL(@Cols+',','')+'[Col '+LTRIM(sv.number)+']' FROM master.dbo.spt_values as sv WHERE  sv.Type='P' and sv.number BETWEEN 1 AND @MaxWordCount

PRINT @Cols
SET @sql='SELECT * from (
             SELECT t.*, w.* FROM #tb AS t
             CROSS APPLY (VALUES (convert(XML, ''<n>'' + replace(t.Column1, '' '', ''</n><n>'') + ''</n>''))) x(c)
             CROSS APPLY (SELECT ''Col '' + ltrim(row_number()OVER (ORDER BY getdate()))  AS col
                                 ,s.b.value(''.'', ''varchar(200)'') AS wd
                          FROM x.c.nodes(''n'') s(b)) w
         ) a PIVOT (max(wd) for col in ('+@Cols+')) p'
PRINT @sql
EXEC(@sql)
IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb

id  Column1 Col 1   Col 2   Col 3   Col 4   Col 5   Col 6
2   w1 w2 w3 w4 w5 w6   w1  w2  w3  w4  w5  w6
1   word1 word2 word3 word  word1   word2   word3   word    NULL    NULL

Answer 4

here is a dynamic sql version. of John's in case you don't know the maximum number of words. Key techniques to accomplish what you want would be split string and pivot (or conditional aggregation). Because you are kind of doing both at once John's method is a nice shortcut.

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    ID INT IDENTITY(1,1)
    ,String VARCHAR(500)
)

INSERT INTO #TblName VALUES ('word1  word2  word3  word4'),('abcd  efgh  ijkl')

DECLARE @NumWords INT

SELECT @NumWords = ISNULL(MAX((LEN(String) - LEN(REPLACE(String,'  ','')))/2 + 1), 0)
FROM
    #TblName

DECLARE @i INT = 1
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
SELECT
    t.Id
    ,t.String
    ,c.*
FROM
    #TblName t 
    CROSS APPLY (
       SELECT

          '
WHILE @i <= @NumWords
BEGIN
    SET @SQL = @SQL
             + IIF(@i > 1,', ','')
             + 'Column' + CAST(@i AS NVARCHAR(MAX)) + '1 = x.value (''/x[' + CAST(@I AS NVARCHAR(MAX)) + ']'',''varchar(max)'')'

    SET @i = @i + 1
END

SET @SQL = @SQL + '
       FROM
          (SELECT CAST(''<x>'' + REPLACE(String,''  '',''</x><x>'') + ''</x>'' as XML) x) a
    ) c'


EXECUTE (@SQL)

Answer 5

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