sql – 使用CTE进行字符串拆分的有效方法

我有一张看起来像的桌子

ID  Layout
1   hello,world,welcome,to,tsql
2   welcome,stackoverflow

期望的输出应该是

Id  Splitdata
1   hello
1   world
1   welcome
1   to
1   tsql
2   welcome
2   to
2   stackoverflow

我已通过以下查询完成此操作

Declare @t TABLE(
    ID  INT IDENTITY PRIMARY KEY,Layout VARCHAR(MAX)
)
INSERT INTO @t(Layout)
SELECT 'hello,tsql' union all
SELECT 'welcome,stackoverflow'
--SELECT * FROM @t
;With cte AS(
select F1.id,O.splitdata 
 from
 (
 select *,cast('<X>'+replace(F.Layout,','</X><X>')+'</X>' as XML) as xmlfilter
 from @t F
 )F1
 cross apply
 ( 
 select fdata.D.value('.','varchar(MAX)') as splitdata 
 from f1.xmlfilter.nodes('X') as fdata(D)) O
 )

 select * from cte

但表现明智,这是非常糟糕的.我正在寻找一个更有效的查询,但只使用CTE.

请帮忙

谢谢

解决方法

你似乎已经开始使用CTE了,所以试试这个：

DECLARE @YourTable table (RowID int,Layout varchar(200))
INSERT @YourTable VALUES (1,'hello,tsql')
INSERT @YourTable VALUES (2,'welcome,stackoverflow')

;WITH SplitSting AS
(
    SELECT
        RowID,LEFT(Layout,CHARINDEX(',Layout)-1) AS Part,RIGHT(Layout,LEN(Layout)-CHARINDEX(',Layout)) AS Remainder
        FROM @YourTable
        WHERE Layout IS NOT NULL AND CHARINDEX(',Layout)>0
    UNION ALL
    SELECT
        RowID,LEFT(Remainder,Remainder)-1),RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',Remainder)>0
    UNION ALL
    SELECT
        RowID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',Remainder)=0
)
SELECT * FROM SplitSting ORDER BY RowID

OUTPUT：

RowID       Part                   
----------- -----------------------
1           hello                  
1           world                  
1           welcome                
1           to                     
1           tsql                   
2           welcome                
2           to                     
2           stackoverflow          

(8 row(s) affected)

这是一篇关于在sql Server中分割字符串的优秀文章：“Arrays and Lists in SQL Server 2005 and Beyond,When Table Value Parameters Do Not Cut it” by Erland Sommarskog

编辑这里的另一个版本(但你需要一个数字表)返回与上面相同的结果：

;WITH SplitValues AS
(
    SELECT
        RowID,ListValue
        FROM (SELECT
                  RowID,LTRIM(RTRIM(SUBSTRING(List2,number+1,List2,number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT RowID,' + Layout + ',' AS List2
                           FROM @YourTable
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2,number,1) = ','
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
)
SELECT * FROM SplitValues

请看这里的数字表：What is the best way to create and populate a numbers table?

sql – 使用CTE进行字符串拆分的有效方法

解决方法

猜你在找的MsSQL相关文章