T-SQL: Separate String Into Multiple Columns

问题:

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?

回答1:

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)

回答2:

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

回答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

回答4:

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