拆分分隔的SQL列值成多行拆分分隔的SQL列值成多行(Splitting delimited val

我真的很喜欢这里的一些建议，给一些背景资料，我从Exchange 2007中插入邮件跟踪日志到SQL工作。 因为我们有千百万行的，每天我使用的是BULK INSERT语句将数据插入到SQL表。

其实我真正批量插入到一个临时表，然后从那里我将数据合并到活动表，这是测试分析问题的某些字段，否则有大约值引号和等。

这种运作良好，有一个事实，即收件人地址栏是由分隔一个定界的领域除外; 字符，它可以非常长，有时是可以有许多电子邮件收件人。

我想利用此列，值分成其中随后将被插入到另一个表的多个行。 问题是什么，我想要么时间太长或不工作我想要的方式。

就拿这个例子中的数据：

message-id                                              recipient-address
[e-mail]   [e-mail]
[e-mail]     [e-mail]
[e-mail]              [e-mail];[e-mail];[e-mail]

我想这是跟着我的收件人表进行格式化：

message-id                                              recipient-address
[e-mail]   [e-mail]
[e-mail]     [e-mail]
[e-mail]              [e-mail]
[e-mail]              [e-mail]
[e-mail]              [e-mail]

有没有人有我怎么能去这样做的任何想法？

我知道PowerShell的非常好，所以我在尝试，但foreach循环甚至在28K记录了永远的过程，我需要的东西，将运行快速/高效地。

谢谢！

Answer 1:

首先，创建一个分割功能：

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

现在，你可以通过简单地推断：

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

此外，我建议不要把短线列名。 这意味着你总是把它们放在[square brackets] 。

Answer 2:

SQL服务器2016包括一个新的表函数string_split（），类似于以前的解决方案。

唯一的要求是设置兼容级别设置为130（SQL服务器2016）

Answer 3:

您可以使用CROSS APPLY （可在SQL Server 2005及以上版本）和STRING_SPLIT功能（在SQL Server 2016和以上版本）：

DECLARE @delimiter nvarchar(255) = ';';

-- create tables
CREATE TABLE MessageRecipients (MessageId int, Recipients nvarchar(max));
CREATE TABLE MessageRecipient (MessageId int, Recipient nvarchar(max));

-- insert data
INSERT INTO MessageRecipients VALUES (1, '[e-mail]; [e-mail]; [e-mail]');
INSERT INTO MessageRecipients VALUES (2, '[e-mail]; [e-mail]');

-- insert into MessageRecipient
INSERT INTO MessageRecipient
SELECT MessageId, ltrim(rtrim(value))
FROM MessageRecipients 
CROSS APPLY STRING_SPLIT(Recipients, @delimiter)

-- output results
SELECT * FROM MessageRecipients;
SELECT * FROM MessageRecipient;

-- delete tables
DROP TABLE MessageRecipients;
DROP TABLE MessageRecipient;

结果：

MessageId   Recipients
----------- ----------------------------------------------------
1           [e-mail]; [e-mail]; [e-mail]
2           [e-mail]; [e-mail]

和

MessageId   Recipient
----------- ----------------
1           [e-mail]
1           [e-mail]
1           [e-mail]
2           [e-mail]
2           [e-mail]

文章来源: Splitting delimited values in a SQL column into multiple rows