Get Index Position of Nth Occurrence of a Character in String in Azure Data Factory

Problem Statement

There is no direct function in ADF / Fabric Data Pipeline to get the Index position of a character in a string.

Is it possible to get the Index Position of the Nth Occurrence of a Character in String in Azure Data Factory / Fabric Data Pipeline?

Prerequisites

1. Azure Data Factory / Synapse Pipeline / Fabric Data Pipeline.

Solution

The first step would be to split the input String into an Array of characters.

1. The initial 2 activities in the above flow: Set variable & For each are for #1.
2. The blog Split a String into an Array of Characters with No Delimiter in Azure Data Factory provides the details for the same.
3. Next, we would need to iterate over the String Array length for each character comparison.
   
4. Check whether the current iteration character is equivalent to the Search character.
   

   @equals(variables('StringArray')[item()], pipeline().parameters.SearchCharacter)

5. If the character matches then follow the below flow, else skip.
   
6. Have a counter to count the occurrence of that character within the string.
   
7. In ADF, a variable cannot be self-referenced within the Set variable activity; hence we need an intermediate variable.
   
8. Start capturing the index position of the Character occurrence.
   
9. Set the Temp Index position if the Count of the occurrence matches within the Nth Occurrence parameter.
   

   @if(
       equals(variables('Count'), pipeline().parameters.OccurenceCount),
       string(item()),
       variables('FinalPositionIndex')
   )
   

10. Assign the Final value for the output via Set Variable activity.

There would be 4 scenarios.

1. In case of not even a single existing character within the String: No existence of Character by leveraging the Contains function.
2. In case there is no Nth occurrence of the character within the string: No Matching Index Position.
3. In case if the character is in the 1st position (Index 0).
4. Nth Index of the Character occurrence.
   

   @if(
       not(contains(pipeline().parameters.InputString, pipeline().parameters.SearchCharacter)),
       'No existence of Character',
       if(
           equals(variables('PositionIndex'), ''),
           'No Matching Index Position',
           string(variables('PositionIndex'))
       )
   )

Result

Scenario 1. Not even a single existence of character within the String.

Input

Output

Scenario 2. No Nth occurrence of the character within the string.

Input

Output

Scenario 3. Character is in the 1st position (Index 0).

Input

Output

Scenario 4. Nth Index of the Character Occurrence.

Input

Output