Splitting delimited values in a SQL column into multiple rows

I would really like some advice here, to give some background info I am working with inserting Message Tracking logs from Exchange 2007 into SQL. As we have millions upon millions of rows per day I am using a Bulk Insert statement to insert the data into a SQL table.

In fact I actually Bulk Insert into a temp table and then from there I MERGE the data into the live table, this is for test parsing issues as certain fields otherwise have quotes and such around the values.

This works well, with the exception of the fact that the recipient-address column is a delimited field seperated by a ; character, and it can be incredibly long sometimes as there can be many email recipients.

I would like to take this column, and split the values into multiple rows which would then be inserted into another table. Problem is anything I am trying is either taking too long or not working the way I want.

Take this example data:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com;user4@domain4.com;user5@domain5.com

I would like this to be formatted as followed in my Recipients table:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user4@domain4.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user5@domain5.com

Does anyone have any ideas about how I can go about doing this?

I know PowerShell pretty well, so I tried in that, but a foreach loop even on 28K records took forever to process, I need something that will run as quickly/efficiently as possible.

Thanks!

Answers


First, create a split function:

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

Now you can extrapolate simply by:

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

Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].


SQL Server 2016 include a new table function string_split(), similar to the previous solution.

The only requirement is Set compatibility level to 130 (SQL Server 2016)


You may use CROSS APPLY (available in SQL Server 2005 and above) and STRING_SPLIT function (available in SQL Server 2016 and above):

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, 'user1@domain.com; user2@domain.com; user3@domain.com');
INSERT INTO MessageRecipients VALUES (2, 'user@domain1.com; user@domain2.com');

-- 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;

Results:

MessageId   Recipients
----------- ----------------------------------------------------
1           user1@domain.com; user2@domain.com; user3@domain.com
2           user@domain1.com; user@domain2.com

and

MessageId   Recipient
----------- ----------------
1           user1@domain.com
1           user2@domain.com
1           user3@domain.com
2           user@domain1.com
2           user@domain2.com

Need Your Help

LG G4 crashes on start of Lunar Lander-based Android game

java android crash jvm-crash lg

I wrote an Android game based on Google's Lunar Lander example (using the Android Canvas for 2D drawing). The game has worked fine on all Android devices since 2009, except users have recently repo...