1

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 .     a@a.com,b@b.com,c@c.com

I want to be looks like this;

ID .     Source
1.       a@a.com
1.       b@b.com
1.       c@c.com

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

  • 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 Mar 8 at 23:17
5

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', 'a@a.com,b@b.com,c@c.com'),
    ('2', 'd@d.com,e@e.com,f@f.com')
;

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

  • 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 Mar 9 at 21:17
  • Updated my answer. – Adam Spriggs Mar 10 at 2:47
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

  • 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 Mar 8 at 22:12
  • Anyone please.. ???? – Sohail Mar 8 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 Mar 8 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 Mar 8 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.