find character position of nth occurrence in a string in ADF

Question

I have a field of string data and need to find nth occurrence of character position using ADF.

example:

instr('microsoft is good')

first occurrence: 5

second occurrence: 7

third occurrence: 15

fourth occurrence: 16

Answer 1

Hi Peruka, Sikander reddy ,

Thankyou for posting your query on Microsoft Q&A platform .

I understand that you are trying to fetch the index of each occurrences of a character in a string using ADF pipeline.

Unfortunately, ADF Data Flow does not have a direct function to find the nth occurrence, but you can achieve this by creating cascading approach where each occurrence is based on the previous one with locale in derived column transformation.

To directly find the nth occurrence of a character in a string using Azure Data Factory (ADF) Data Flow, you can create a custom logic using a combination of expressions.

If you're sharing logic across multiple columns or want to compartmentalize your logic, you can create a local variable : Locals

Create the columns with these locals:

Hope it helps. Kindly accept the answer by clicking on Accept answer. Thankyou

Answer 2

Another alternate way without using Dataflow would be via leveraging foreach activity and converting the string into an array.

The below blog :

https://datasharkx.wordpress.com/2024/07/07/get-index-position-of-nth-occurrence-of-a-character-in-string-in-azure-data-factory-fabric-data-pipeline/

provides the details for the same.

Note : you can call this pipeline in another pipeline if you want all index positions of the character