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?
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)
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
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
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
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
