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
abc@test.com, bcd@gmail.com           Company A
hello@yahoo.com, sayonara@gmail.com   Company B

My goal is to make a result table like this:

Result               Account
abc@test.com         Company A
bcd@gmail.com        Company A
hello@yahoo.com      Company B
sayonara@gmail.com   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
abc@test.com, bcd@gmail.com           ,         Company A
hello@yahoo.com, sayonara@gmail.com   ,         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, Jan 27 '15 at 15:53
You are using your function with the wrong parameters, should be `dbo.SplitAgain(',',email_address)` — Lamak, Jan 27 '15 at 15:54
I'd also recommend reading the following articles: [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up). [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/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, Jan 27 '15 at 15:58
DOH! I guess it's too early here without coffee... thanks much!! — ichachan, Jan 27 '15 at 16:10

score 1 · Answer 1 · answered Jan 27 '15 at 15:56

1

You have your parameter order reversed

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

answered Jan 27 '15 at 15:56

SoulTrain

1,904
1
12
11

Splitting String in SQL Column into Multiple Rows

1 Answers1