2

I have a table which have two fields ID and Source. Source col. have multiple emails with comma separated. I want to create a separate record for each email with same ID.

So data looks like this

ID .    Source  
1 .     [email protected],[email protected],[email protected]

I want to be looks like this;

ID .     Source
1.       [email protected]
1.       [email protected]
1.       [email protected]

I tried using "STRING_SPLIT" function but it didn't work, SFMC didn't recognize this function. Any other work around???

Improve this question
1
  • Just now tried query.. SELECT id, SUBSTRING(',' + email + ',', n + 1, CHARINDEX(',', ',' + email + ',', n + 1) - n - 1) AS email FROM Numbers JOIN stest ON (SUBSTRING(',' + email + ',', n, 1 ) = ',' AND n < LEN(',' +email + ',')) But again getting error "An error occurred while checking the query syntax. Errors: Argument data type nvarchar is invalid for argument 2 of substring function."
    – Sohail
    Commented Mar 8, 2019 at 23:17

2 Answers 2

Reset to default
6

You can use CROSS APPLYs to unfurl the CSV list:

Schema: 

CREATE TABLE ListSubs
    ([list] varchar(13), [subscribers] varchar(500))
;

INSERT INTO ListSubs
    ([list], [subscribers])
VALUES
    ('1', '[email protected],[email protected],[email protected]'),
    ('2', '[email protected],[email protected],[email protected]')
;

Query:

SELECT
  a.list
  , a.subscribers
  , SUBSTRING(a.subscribers, 1, ISNULL(p1 - 1, LEN(a.subscribers))) sub1
  , SUBSTRING(a.subscribers, p1 + 1, ISNULL(p2, LEN(a.subscribers) + 1) - p1 - 1) sub2
  , SUBSTRING(a.subscribers, p2 + 1, ISNULL(p3, LEN(a.subscribers) + 1) - p2 - 1) sub3
  , SUBSTRING(a.subscribers, p3 + 1, ISNULL(p4, LEN(a.subscribers) + 1) - p3 - 1) sub4
  , SUBSTRING(a.subscribers, p4 + 1, ISNULL(p5, LEN(a.subscribers) + 1) - p4 - 1) sub5
  , SUBSTRING(a.subscribers, p5 + 1, ISNULL(p6, LEN(a.subscribers) + 1) - p5 - 1) sub6
  , SUBSTRING(a.subscribers, p6 + 1, ISNULL(p7, LEN(a.subscribers) + 1) - p6 - 1) sub7
from ListSubs a
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p3 + 1), 0)) e(p4)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p4 + 1), 0)) f(p5)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p5 + 1), 0)) g(p6)
CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p6 + 1), 0)) h(p7)
where isnull(a.subscribers,'') != ''

SQLFiddle.

If you want them in individual rows, then you could do this using the same method by splitting everything out in unions:

select 
  x.list
, x.sub
from (

    SELECT
    a.list
    , SUBSTRING(a.subscribers, 1, ISNULL(p1 - 1, LEN(a.subscribers))) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p1 + 1, ISNULL(p2, LEN(a.subscribers) + 1) - p1 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p2 + 1, ISNULL(p3, LEN(a.subscribers) + 1) - p2 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p3 + 1, ISNULL(p4, LEN(a.subscribers) + 1) - p3 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p3 + 1), 0)) e(p4)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p4 + 1, ISNULL(p5, LEN(a.subscribers) + 1) - p4 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p3 + 1), 0)) e(p4)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p4 + 1), 0)) f(p5)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p5 + 1, ISNULL(p6, LEN(a.subscribers) + 1) - p5 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p3 + 1), 0)) e(p4)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p4 + 1), 0)) f(p5)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p5 + 1), 0)) g(p6)

    union 

    SELECT
    a.list
    , SUBSTRING(a.subscribers, p6 + 1, ISNULL(p7, LEN(a.subscribers) + 1) - p6 - 1) sub
    from ListSubs a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p3 + 1), 0)) e(p4)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p4 + 1), 0)) f(p5)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p5 + 1), 0)) g(p6)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.subscribers, p6 + 1), 0)) h(p7)
) x 
where x.sub is not null

SQLFiddle

Improve this answer
3
  • Thanks Adam. But this solution will create a seprate column for each email. I am looking for create a seprate row/record for each id. Any idea accomplish this ?
    – Sohail
    Commented Mar 9, 2019 at 21:17
  • Updated my answer.
    – Adam Spriggs
    Commented Mar 10, 2019 at 2:47
  • Does this solution only work with a fixed number of items?
    – Stan Alachniewicz
    Commented Jul 16 at 21:35
0

You may be able to figure out some implementation using SubString which Marketing CLoud does not seem to have a problem with.

For instance, this will get you the first email address: 

SELECT SUBSTRING([source], 1, CASE CHARINDEX(',', [source])
        WHEN 0
            THEN LEN([source])
        ELSE CHARINDEX(',', [source]) - 1
        END) AS Email
FROM _table

Can you expand upon that to fit your needs?

Implementations are really limited without user functions, variables, temp tables which are not supported based upon the documentation here: https://help.salesforce.com/articleView?id=mc_as_sql_reference.htm&type=5

Improve this answer
4
  • Thank you Mike. I tried the below query... SELECT id, SUBSTRING(source, 1, CASE CHARINDEX(',', source) WHEN 0 THEN LEN(source) ELSE CHARINDEX(',', source) - 1 END) AS Email FROM stest But this query only brings 1 email and finish. How do i split all email?
    – Sohail
    Commented Mar 8, 2019 at 22:12
  • Anyone please.. ????
    – Sohail
    Commented Mar 8, 2019 at 22:35
  • Do you have a set number of emails per row or is it variable? I am having difficulty thinking of an exact implementation due to the marketing cloud sql limitations. Here is another question and an answer: salesforce.stackexchange.com/questions/190776/…
    – Mike
    Commented Mar 8, 2019 at 22:39
  • Well their is no set number of emails per row. It can be 1, it can be 5. But so far I found not more than 5 emails. So lets assume If I will have max 5 emails, then what's the solution. Thanks again Mike.
    – Sohail
    Commented Mar 8, 2019 at 22:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .