This is a quick blog showing how to do a string split to get particular items in ADF data flows. Consider the following data where names and colours are combined into the FullName and Colours columns respectively. Note the delimiter for FullName is a space and the delimiter for Colours is a comma.
To get each individual item and create new columns for this data use the split function in a Derived column transformation. The syntax for this function is split(‘<text>, ‘<delimiter>’). We have two different delimiters so we will have two different versions of this function for the two source columns:
- split(FullName,’ ‘)
- split(Colours,’,’)
The function on its own returns an array so to get particular items we need to specify the item(s) we want, note this is 1-based so for the first item use 1. To get the full list of items in separate rows the transformation settings are as follows:
If we now preview the data we can see the original columns alongside the new derived columns as expected:
Note you can handle nulls using the iif and isNull functions, for example iif(isNull(split(Colours,’,’)[4]), ”, split(Colours,’,’)[4]). Hopefully you find this useful, it helped me complete a very specific requirement in a simple way.
