In Azure Data Factory (ADF), we often use expressions to manipulate strings and extract specific parts of data. One common operation is splitting strings based on a delimiter. However, this can sometimes lead to errors, especially if the string format is not consistent. In this blog post, we’ll explore a common issue with the split function in ADF and how to handle it gracefully.

The Problem

Consider the following scenario: you have a variable fullItemName that contains filenames in the format prefix_filename.extension, and you want to extract the filename part. A common approach is to use the split function, as shown below:

@split(split(variables('fullItemName'),'_')[1],'.')[0]

This expression attempts to split the string twice:

1. First, it splits the fullItemName using the underscore _ delimiter.
2. Then, it accesses the second element (index [1]) from the resulting array.
3. Finally, it splits this element using the period . delimiter to extract the filename.

However, sometimes you might encounter the following error:

The expression 'split(split(variables('fullItemName'),'_')[1],'.')[0]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array.

This error occurs when the fullItemName does not contain the expected _ character, resulting in an array with fewer elements than expected.

The Solution

To handle this issue, we need to ensure that the array has enough elements before attempting to access them. This can be achieved using conditional logic in the ADF expression. Here’s a revised expression that safely checks the array length before accessing it:

@if(
    greaterOrEquals(
        length(split(variables('fullItemName'), '_')), 
        2
    ), 
    split(split(variables('fullItemName'), '_')[1], '.')[0], 
    ''
)

How It Works

1. Split the String: The expression first splits the fullItemName by the _ delimiter.
2. Check Array Length: It uses the length function to determine the number of elements in the array. The greaterOrEquals function checks if the array length is at least 2, ensuring that there are enough elements to safely access the second element (index [1]).
3. Conditional Logic: The if function evaluates the condition. If the array has at least two elements, it proceeds to split the second element by . and extracts the first part. If not, it returns an empty string '', indicating that the expected pattern was not found.

Example Usage

Suppose you have the following filenames:

1. item_file1.txt
2. singlefile.txt

Using the above expression:

• For item_file1.txt, the result would be file1 because it meets the expected format.
• For singlefile.txt, the result would be '' (empty string) because there is no _ to split on, preventing an out-of-bounds error.

Conclusion

Handling unexpected data formats is crucial in any data processing workflow. By using conditional logic in Azure Data Factory, you can create more robust and error-resistant pipelines. The if function, combined with greaterOrEquals and length, provides a powerful way to handle split operations safely.