SQL 使用 CTE 进行字符串拆分的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6381587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficient way to string split using CTE
提问by aditi
I have a table that looks like
我有一张看起来像的桌子
ID Layout
1 hello,world,welcome,to,tsql
2 welcome,to,stackoverflow
The desired output should be
所需的输出应该是
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
But performance wise it is very bad. I am looking for a more efficient query but using CTE only.
但从性能上来说，这是非常糟糕的。我正在寻找更有效的查询，但仅使用 CTE。
回答by KM.
You seem dead set on using a CTE, so try this:
你似乎死心塌地使用 CTE，所以试试这个：
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
OUTPUT:
输出：
RowID Part
----------- -----------------------
1 hello
1 world
1 welcome
1 to
1 tsql
2 welcome
2 to
2 stackoverflow
(8 row(s) affected)
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
这是一篇关于在 SQL Server 中拆分字符串的出色文章：Erland Sommarskog 撰写的“SQL Server 2005 及更高版本中的数组和列表，当表值参数不剪切时”
EDIThere'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
see here for a numbers table: What is the best way to create and populate a numbers table?
有关数字表，请参见此处：创建和填充数字表的最佳方法是什么？
回答by Wesley Salmi
it's my best solution using CTE:
这是我使用 CTE 的最佳解决方案：
DECLARE @Char VARCHAR(MAX) = '10||3112||||aaaa||'
DECLARE @Separador CHAR(2) = '||'
;WITH Entrada AS(
SELECT
CAST(1 AS Int) As Inicio,
CHARINDEX(@Separador, @Char) As Fim
UNION ALL
SELECT
CAST(Fim + LEN(@Separador) AS Int) As Inicio,
CHARINDEX(@Separador, @Char, Fim + 1) As Fim
FROM Entrada
WHERE CHARINDEX(@Separador, @Char, Fim + 1) > 0
)
SELECT
SUBSTRING(@Char, Inicio, Fim - Inicio)
FROM Entrada
WHERE (Fim - Inicio) > 0
回答by Arun Prasad E S
From NullRef's Answer
来自 NullRef 的回答
Function without set operation will be faster according to my understanding of sql server
根据我对sql server的理解，没有set操作的函数会更快
so this will be very efficient
所以这将非常有效
CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
WITH a AS(
SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
UNION ALL
SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
FROM a
WHERE idx2>0
)
SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
FROM a
