Splitting String in SQL Column into Multiple Rows

Question

I have a table consisting of list of email addresses separated by comma and the account name. Basically my goal is to separate them per email address.

So my initial table is like this:

Email                                 Account
[email protected], [email protected]           Company A
[email protected], [email protected]   Company B

My goal is to make a result table like this:

Result               Account
[email protected]         Company A
[email protected]        Company A
[email protected]      Company B
[email protected]   Company B

So I read a solution about creating a split function from here: T-SQL: Opposite to string concatenation - how to split string into multiple records

So this is my Split function:

create FUNCTION [dbo].[SplitAgain] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

Then I do a SELECT from my table like below, I suppose the result of the function would be stored in the column 'Item'.

SELECT email_address,Item, account
FROM contact 
CROSS APPLY dbo.SplitAgain(email_address,',')

However I'm confused because the result is something like this... the resulted 'Item' is only the commas.

Email                                 Result    Account
[email protected], [email protected]           ,         Company A
[email protected], [email protected]   ,         Company B

I'm definitely missing something. Any help is appreciated!

-icha

You are passing the parameters in the wrong order, you should be using CROSS APPLY dbo.SplitAgain(',', email_address), the delimiter is the first argument in the function. - create FUNCTION [dbo].[SplitAgain] (@sep VARCHAR(32), @s VARCHAR(MAX)) — GarethD
– GarethD, Commented Jan 27, 2015 at 15:53
You are using your function with the wrong parameters, should be dbo.SplitAgain(',',email_address) — Lamak
– Lamak, Commented Jan 27, 2015 at 15:54
I'd also recommend reading the following articles: Split strings the right way – or the next best way, Splitting Strings : A Follow-Up. Splitting Strings : Now with less T-SQL. There are hundreds, if not thousands of questions on Stackoverflow about splitting strings and I don't think any of them are as comprehensive as those articles. — GarethD
– GarethD, Commented Jan 27, 2015 at 15:58
DOH! I guess it's too early here without coffee... thanks much!! — ichachan
– ichachan, Commented Jan 27, 2015 at 16:10

SoulTrain · Accepted Answer · 2015-01-27 15:56:43Z

1

You have your parameter order reversed

SELECT email_address,Item, account
FROM contact 
CROSS APPLY dbo.SplitAgain(',',email_address)

answered Jan 27, 2015 at 15:56

SoulTrain

1,9041 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Splitting String in SQL Column into Multiple Rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related