想要一个存储过程以逗号分隔的字符串，该字符串是表中的一列（具有20000行）

小编典典

想要一个存储过程以逗号分隔的字符串，该字符串是表中的一列（具有20000行）

sql

我有一个具有20000行的表，并且其中有一列用逗号分隔的字符串。我想从每一行中拆分每个逗号分隔的字符串，并将它们逐行插入到临时表中，不要重复。我想为此表创建一个存储过程，该存储过程具有表名和列作为该存储过程的参数。

这是桌子

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')

我有单个字符串的登录名，但是我不知道如何处理给定列中的这2万行用逗号分隔的字符串-有帮助吗？

阅读 191

2021-05-05

共1个答案

小编典典

使用aa Parse进行交叉申请应该可以解决问题

选项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)

2021-05-05