CODEWITHSUNDEEP
sql
ichachan
ichachan

Reputation: 667

Splitting String in SQL Column into Multiple Rows

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

Upvotes: 0

Views: 1877

Answers (1)

SoulTrain
SoulTrain

Reputation: 1904

You have your parameter order reversed

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

Upvotes: 1

Related Questions