T-SQL:将字符串分成多列 [英] T-SQL: Separate String Into Multiple Columns

问题描述

例如

Column 1:
| word1 word2 word3 word4 |

到

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

是否可以将字符串中的不同单词或短语分成多列?字符串中的所有单词和短语通常用双空格分隔，仅此而已.是否有可以从 SQL Server 中使用的预定义函数，例如 CAST 或 INTERSECT，还是我必须自己编写?

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?

推荐答案

这里是一个动态的 sql 版本.如果您不知道最大字数，请使用约翰的.完成您想要的关键技术是拆分字符串和数据透视(或条件聚合).因为您可以同时进行两种操作，所以 John 的方法是一个不错的捷径.

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)

这篇关于T-SQL:将字符串分成多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！