find character position of nth occurrence in a string in ADF

Peruka, Sikander reddy 65 Reputation points
2024-07-05T21:58:29.1233333+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,631 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,033 questions
0 comments
Accepted answer
  1. AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee
    2024-07-06T09:39:59.6166667+00:00

    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

    User's image

    User's image

    User's image

    User's image

    User's image

    Create the columns with these locals:

    User's image

    User's image

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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 30,871 Reputation points MVP
    2024-07-07T08:23:05.94+00:00

    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

    0 comments