Splitting CONCAT data in Marketing Cloud [closed]

Question

We are receiving data from our Sales Cloud instance via MCC connector, and in one of the fields tied to a personAccount - "Agent_Names" - we have multiple string values (i.e.support agents' names) concatenated / divided with a comma ",". For example, John Smith,John Doe,Mike Mikeson,... We would need to work with this data in Marketing Cloud in 2 ways:

1. Directly in email body (Email Studio/Content Builder) where we would need to e.g. list the names of all support agents the customer can contact through AMPscrip, and
2. In Automation Studio - where we would need to either divide this data across other fields in a data extension (SSJS?) or do some filtering based on such data.

What are your suggestions for these 2 use case, other than going to our Sales Cloud team and asking for better data :).
Thanks

Answer 1

As @JonasLamberty stated in his comment, you would want to do BuildRowsetFromString() in AMPscript to be able to use it in an email.

BuildRowsetFromString():

SET @myDelimitedField = "first,second,third"

SET @rows = BuildRowsetFromString(@myDelimitedField,",")

FOR @i=1 TO Rowcount(@rows) DO
  SET @myValue = Field(Row(@rows,@i),1)
NEXT @i

In SQL you have a couple options, including CROSS APPLY as @JonasLamberty said.

CROSS APPLY:

SELECT    p.Email
        , p.FieldA
        , c.FieldB
FROM [testParse] p
CROSS APPLY (
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as FieldB1
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[2]','nvarchar(max)') as FieldB2
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') as FieldB3
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[4]','nvarchar(max)') as FieldB4
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[5]','nvarchar(max)') as FieldB5
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[6]','nvarchar(max)') as FieldB6
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[7]','nvarchar(max)') as FieldB7
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[8]','nvarchar(max)') as FieldB8
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[9]','nvarchar(max)') as FieldB9
) c (FieldB)
WHERE ISNULL(c.FieldB,'') <> ''

and also turning the whole delimited string into XML and using the Value() function.

SELECT pkey,
CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as firstNode,
CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML).value('/x[2]','nvarchar(max)') as secondNode,
CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') as thirdNode,
CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML).value('/x[4]','nvarchar(max)') as fourthNode
FROM [myDE]

Which will output each part of the string up to 4 values - you can adjust the number of nodes to include the number of things inside of the field.

Then also inside of SSJS you can use Split to turn the string into an array and then use index notation to pull the different values.

var myString = 'first,second,third'
var myArr = myString.Split(',');
//["first","second","third"]

var val1 = myArr[0]
//first

var val2 = myArr[1]
//second

var val3 = myArr[2]
//third