If you have a delimited string, it can be a hassle trying to gather specific values from it and most effective methods are not accepted in SFMC SQL. The best solution I have been able to find is via the XML ‘value’ function.

Basically what this does is take a declared section of XML and then pull a specific value from it. Super useful, right? Except, I have a delimited string and not xml…how will this help?

Its simple….well not really, its hacky as all ‘creative’ solutions are for SFMC, but once you know how it works, its pretty simple.

1. You take your delimited string and use some Concats and Replaces to turn it into faux XML (but is still a String datatype).
2. You use a CAST or CONVERT to change the datatype to XML and make it legit (remember to account for any illegal XML chars ( ‘<>”&) as this will cause an error in your query.)
3. You then have a ready to go XML block to run your Value() function on

Lets go through this step by step:

Turn a delimited string to an ‘xml string’:

'<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>'

Outputting:
<x>a</x><x>b</x><x>c</x><x>d</x>

The above SQL will start and end (via the + as a concat) the string with an open/close node for your XML. You then use a replace to replace your delimiter (in this case |) with ‘</x><x>’ to close then open your XML node.

Change DataType to XML:

CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML)

I used a CAST() but you can also use a CONVERT() as well to do this. Convert(XML, yourXMLStr)

XML is now ready for Value() function

CAST('<x>' + REPLACE('a|b|c|d','|','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)')

This will then return the value from the 3rd ‘node’ you created, essentially the 3rd value in your delimited string. So in this case, the output for our code would be: c

Lets look into this a bit further into the Value() function:

Argument 1: '/x[3]' – this is the part where you define the node as well as the iterative of that node you want to look at. If you had multiple nodes, you could create a path here to dig down and find the specific value you need. *Note that this can only return a single value, if you attempt to return more than one value, it will cause an error.

Argument 2: 'nvarchar(max)' sets the returned value datatype. This way you can return a value that is not XML, but can be a number, text, date, etc. and not need to do an extra Cast or Convert, etc. on it.