Introduction

Azure Data Factory (ADF) is a powerful cloud-based data integration service that allows you to create data-driven workflows for orchestrating and automating data movement and data transformation. When working with data, it’s common to encounter situations where you need to manipulate strings, such as splitting a string by a delimiter and accessing specific elements. In this post, we will explore how to use ADF expressions to split a string and take the first element.

The Problem

While working with strings in ADF, you might encounter a scenario where you need to split a string by a specific delimiter and use only the first part of the resulting array. A typical error message you might see when trying to pass an array to a function expecting a string is:

Cannot fit string list item into the function parameter string.(6)

This error occurs because the function expects a single string value, not an array or list.

The Solution

To solve this issue, you need to split the string into an array and then access the specific element you want. In ADF, this can be achieved using the split function followed by indexing into the array.

Example Expression

Let’s say you have a string variable called myString with the value "abc-def-ghi", and you want to split it by the - delimiter and take the first part ("abc"). Here’s how you can do it:

split(variables('myString'), '-')[0]

Explanation:

1. split(variables('myString'), '-'): This part of the expression splits the string stored in the myString variable wherever it finds the - delimiter. The result is an array: ["abc", "def", "ghi"].
2. [0]: This indexing operation accesses the first element of the array, which is "abc".

Using the first Function

Another way to achieve the same result is by using the first function, which directly returns the first element of the array produced by the split function:

first(split(variables('myString'), '-'))

When to Use These Methods

• Use split(variables('myString'), '-')[0] when you want to explicitly access the first element of an array.
• Use first(split(variables('myString'), '-')) for a more declarative approach.

Both methods effectively return the first part of the split string, and you can choose the one that fits better with your code style or readability preferences.

Conclusion

Handling strings and splitting them correctly is a common requirement in Azure Data Factory workflows. By understanding how to use the split function and access specific elements, you can avoid common errors and write more effective and efficient expressions. Whether you’re new to ADF or a seasoned user, mastering these basic string manipulation techniques will make your data transformation tasks much easier.