想要一个逗号分隔字符串的存储过程,它是表中的一列(有 20000 行) [英] Want a stored procedure for comma seperated string which is of a column (has 20000 rows ) in a table
问题描述
我有一个包含 20000 行的表,以及一个包含逗号分隔字符串的列.我想从每一行拆分每个逗号分隔的字符串,并将它们逐行插入到临时表中,没有任何重复项.我想为此表创建一个存储过程,并将表名和列作为该存储过程的参数.
I have a table with 20000 rows, and a column that has comma-separated strings in it. I want split each of the comma-separated strings from each row and insert them into a temp table row by row without any duplicates. I want to create a stored procedure for this table with has table name and column as parameters to that stored procedure.
这是表
CREATE TABLE [dbo].[Campaign]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[MISC] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
这是有
insert into Campaign(MISC)
values ('h101,h202,h506,h707,h112,h566'),
('h101,h102,h508,h113,h456,h678'),
('h110,h880,h432,h111,h102,h509,h213'),
('h456,h609,h432,h456,h678,h101,h807')
我有单个字符串的登录名,但我不知道如何处理给定列中这 20000 行逗号分隔的字符串 - 有什么帮助吗?
I have a the login for single string but I don't know how to deal with these 20000 rows of comma-separated strings in the given column - any help?
推荐答案
Cross Apply 和 Parse 应该可以解决问题
A Cross Apply with a a Parse should do the trick
选项 1 - 使用 UDF
Select A.ID
,B.*
From Campaign A
Cross Apply [dbo].[tvf-Str-Parse-8K](A.Misc,',') B
选项 2 - 不使用 UDF
Select A.ID
,B.*
From Campaign A
Cross Apply (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(A.Misc,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) B
两者都返回
ID RetSeq RetVal
1 1 h101
1 2 h202
1 3 h506
1 4 h707
1 5 h112
1 6 h566
2 1 h101
2 2 h102
2 3 h508
...
4 3 h432
4 4 h456
4 5 h678
4 6 h101
4 7 h807
UDF(如果需要)
CREATE FUNCTION [dbo].[tvf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By A.N)
,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
From cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)
这篇关于想要一个逗号分隔字符串的存储过程,它是表中的一列(有 20000 行)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!