How to filter single value from column which is comma separated in data flow ADF

Question

I have a source column which contains some code. The column 'Code' can have single value or comma separated multiple values. example:

1. Code= FA1
2. Code=FA1,MD2
3. Code=GH2,FA1

Now my requirement is to apply filter after source in Data Flow and fetch only those rows which contains FA1.

I cannot use equal as I have to look for row which contain FA1, and my source column is string type not array. I tried in()/instr() and contains function but it did not work for me.

Can anyone please help me to find the correct solution to filter.

Answer 1

You can use the like function in filter transformation to filter rows based on a specific value in a string column that may contain multiple values separated by commas. In this case, you can use the % wildcard character to match any number of characters before or after the value "FA1" in the "Code" column.

Below is the example of how you can use the like function in a Filter transformation to filter rows that contain the value "FA1" in the "Code" column:

• Add a Filter transformation to your Data Flow after the source transformation. In the Filter transformation, Give the expression as like(Code, '%FA1%') in "Filter on" text box.

  

Output of filter transformation: This expression matches any rows where the "Code" column contains the value "FA1" anywhere in the string.