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

2022-01-01 00:00:00 sql common-table-expression sql-server-2005 tsql sql-server

我有一张看起来像的桌子

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 中拆分字符串的优秀文章:"Arrays andSQL 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

EDIT 这是另一个版本(但您需要一个数字表)返回与上述相同的结果:

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

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

相关文章