递归SQL将CSV拆分为表行 [英] Recursive SQL to split CSV to table rows
问题描述
在这里讨论了一个不同的问题在SO,我偶然发现递归CTE,这将表面上似乎是一个相当容易的方法来解决拆分表格行问题。
After working on a different question here on SO, I stumbled across recursive CTEs which would on the surface seem a fairly easy way to solve the "Split a csv to table rows" problem.
我把这个例子放在一起
DECLARE @InputString varchar(255) = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'
SELECT @InputString = @InputString + ','
;
with MyCTE(x,y)
as
(
SELECT
x = SUBSTRING(@InputString,0,PATINDEX('%,%',@InputString)),
y = SUBSTRING(@InputString,PATINDEX('%,%',@InputString)+1,LEN(@InputString))
UNION ALL
SELECT
x = SUBSTRING(y,0,PATINDEX('%,%',y)),
y = SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y))
FROM
MyCTE
WHERE
SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y)) <> '' OR
SUBSTRING(y,0,PATINDEX('%,%',y)) <> ''
)
SELECT x FROM MyCTE
OPTION (MAXRECURSION 2000);
GO
这真的是个坏主意吗?这种递归查询在SQL中的开销是什么,以及这种方法的潜在缺陷是什么。
Is this really a bad idea? What is the overhead in SQL for recursive queries like this, and what are the potential pitfalls for this kind of approach.
顺便说一句,我认为这个想法/技术可能会被用来解决此其他问题。
Incidentally, I'm thinking this idea/technique could probably be leveraged to solve this other question.
推荐答案
这将使用空字符串
DECLARE @InputString VARCHAR(1000)
SELECT @InputString = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,,1'
SELECT SUBSTRING(',' + @InputString + ',', Number + 1,
CHARINDEX(',', ',' + @InputString + ',', Number + 1) - Number -1)AS VALUE
FROM master..spt_values
WHERE type = 'p'
AND Number <= LEN(',' + @InputString + ',') - 1
AND SUBSTRING(',' + @InputString + ',', Number, 1) = ','
GO
另请参阅这里的注释:S / a>其他一些想法
Also take a look at the comments here: Split string in SQL Server 2005+ CLR vs. T-SQL for some other ideas
这篇关于递归SQL将CSV拆分为表行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!