When working with data in Azure Data Factory (ADF), it’s common to deal with delimited strings. You might need to extract the last element from such strings. For instance, given a string like string1/string2/string3, you want to extract the last part, which is string3.

In this blog post, we’ll explore how to achieve this using Azure Data Factory’s expression language with the split function and indexing.

Problem

Suppose you have a string in ADF that is structured like string1/string2/string3, and your goal is to extract the last element, string3. The string is delimited by slashes (/), and you want to isolate the part after the final slash.

Solution

Azure Data Factory provides a simple way to achieve this using the split function, which breaks a string into an array based on a specified delimiter. Once split, you can access the desired element using an index.

Here’s the expression that works for extracting the last element:

@split('string1/string2/string3', '/')[2]

How It Works

• split() function: This function splits a string into an array of substrings based on the delimiter you specify. In this case, the delimiter is /. So, split('string1/string2/string3', '/') will return an array: ['string1', 'string2', 'string3'].
• Indexing: After splitting the string, you can access elements from the array using their index. Array indexing in ADF starts at 0. So, to get the third element (which is string3), you use the index [2].

Example Breakdown

If you want to extract the third part from the string string1/string2/string3, you would use:

@split('string1/string2/string3', '/')[2]

This expression:

• Splits the string into an array: ['string1', 'string2', 'string3']
• Accesses the element at index 2, which gives "string3".

Extracting the Last Element Dynamically

In some cases, the number of delimited parts may vary. If you always want to extract the last element, regardless of the number of elements, you can use the length function in combination with split:

@split('string1/string2/string3', '/')[length(split('string1/string2/string3', '/')) - 1]

This expression works by:

1. Splitting the string into an array.
2. Calculating the length of the array using length().
3. Subtracting 1 from the length to get the index of the last element.
4. Accessing the last element using the calculated index.

Conclusion

Whether you’re dealing with static or dynamic strings, Azure Data Factory provides powerful functions to help you extract specific parts of a string. Using split and indexing, you can easily isolate the last element from a delimited string. For dynamic cases where the number of parts may vary, combining split with length allows you to handle any scenario flexibly.