This one took a LOT of digging and trial and error to figure out. Most of the normal SQL ways to accomplish this (like STRING_SPLIT, etc) are not supported in SFMC, making it heavily reliant on duct tape and bubblegum.

Due to my recent exposure to the XML aspects of SQL, I thought that perhaps turning it into XML and using .value() combined with a CROSS APPLY may work.

After a bit of fiddling and messing around, I figured out that it does work, but it is limited and not exactly efficient.

The CROSS APPLY requires multiple entries and requires defined node values (e.g. 1,2,3,4,5…) in each entry, which puts a ceiling on this query. That is limitation 1, the second limitation is that with 1 through N, the CROSS APPLY will return null values beyond the end of the delimited string. E.g. if string has 4 values and ceiling is 10, it will return 6 null values for 5 through 10.

Luckily, The issue with the null values can be solved with a WHERE statement, excluding those with NULL values for the CROSS APPLY.

Below is the full query:

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,'') <> ''

The main part to pay attention to is the individual sections in the CROSS APPLY:

Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as FieldB1

This takes your delimited list (in this case FieldB and turns it into XML via CAST and REPLACE and combining and then grabs the ‘X’ node value (in this case 1) and outputs it.

Creation of XML

Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML)

First step is to replace all the commas inside Field B with </x><x> to transform each item in the delimited list into an XML node.

Second step is to add <x> to the beginning of the string and </x> at the end to complete the XML.

Finally you CAST the final string as XML data type via CAST().

Output of XML Node Value

.value('/x[1]','nvarchar(max)')

This function (value()) will take the node N ('/x[1] – referenced as 1 here) and outputs it as the data value of 'nvarchar(max)'.

Now this works wonders combined with CROSS APPLY, but as I mentioned before, this has a ceiling.

For instance in my sample, any FieldB delimited strings with more than 9 items will only display the first 9, the rest will be lost.

If you have a relatively small cluster of delimited items, this should work great, but when you have dozens or hundreds of entries, this can get unwieldy. Not to mention this is not exactly efficient, so there is a significant risk of Time out as volume increases.

ALSO, do note that I have WHERE ISNULL(c.FieldB,'') <> '' This is to remove all those values that are run in the cross apply beyond the actual number of delimited items in the string.

For example, if FieldB is 1,2,3,4 – The output would only have 4 rows, not 10 with nulls for 6 of them.