使用CTE进行字符串拆分的有效方法 [英] Efficient way to string split using CTE

问题描述

我有一张看起来像这样的表

I have a table that looks like

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

所需的输出应该是

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

我已经完成了通过下面的查询

I have done this by the below query

Declare @t TABLE(
    ID  INT IDENTITY PRIMARY KEY,
    Layout VARCHAR(MAX)
)
INSERT INTO @t(Layout)
SELECT 'hello,world,welcome,to,tsql' union all
SELECT 'welcome,to,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。

But performance wise it is very bad. I am looking for a more efficient query but using CTE only.

推荐答案

您似乎对使用CTE情有独钟，因此请尝试以下操作：

You seem dead set on using a CTE, so try this:

DECLARE @YourTable table (RowID int, Layout varchar(200))
INSERT @YourTable VALUES (1,'hello,world,welcome,to,tsql')
INSERT @YourTable VALUES (2,'welcome,to,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,CHARINDEX(',',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

输出：

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

(8 row(s) affected)

这是一篇有关在SQL Server中拆分字符串的出色文章：当表值参数无法剪切时，SQL Server 2005及更高版本中的数组和列表，作者：Erland Sommarskog

here is an excellent article on splitting strings in SQL Server: "Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

编辑，这是另一个版本（但您需要一个数字表） ns与上述结果相同：

EDIT here's another version (but you need a numbers table) returns same results as above:

;WITH SplitValues AS
(
    SELECT
        RowID,ListValue
        FROM (SELECT
                  RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', 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

在此处查看数字表：创建和填充数字表格的最佳方法是什么？

这篇关于使用CTE进行字符串拆分的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！