contains function is not working in Data Flow Azure Data Factory

Question

Hi Team,
I been struggling to use contains function in data flow in Azure Data Factory.
here are couple of examples given on below link
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#contains

contains('hello world', 'world')
contains('hello world', 'universe')
Notice that is is saying function expects the array.

When I use the array then it through another error
contains(['test','test1'], 'world')

I have tried everything , nothing is working, I have used contains([1,2,3),3) but getting error as well.
Spark job failed: {
"text/plain": "{\"runId\":\"89273f14-78c8-4369-ba48-9bb5c32225bc\",\"sessionId\":\"7e209d9c-605a-4507-a873-e38c4ef8e036\",\"status\":\"Failed\",\"payload\":{\"statusCode\":400,\"shortMessage\":\"DF-EXPR-021 at Derive 'derivedColumn1'(Line 9/Col 22): Return types do not match\",\"detailedMessage\":\"Failure 2022-08-31 00:15:11.340 failed DebugManager.processJob, run=89273f14-78c8-4369-ba48-9bb5c32225bc, errorMessage=DF-EXPR-021 at Derive 'derivedColumn1'(Line 9/Col 22): Return types do not match\"}}\n"
} - RunId: 89273f14-78c8-4369-ba48-9bb5c32225bc

I have string type column from csv file that have names and I need to check if it contains "A" or some other character. I have wasted my 3 hours and 3 hours of debug money as well:) Any help is appreciated.

Answer 1

Hi @Aamir Shahzad

Thanks for reaching out to MS Q&A portal for posting your question.

In your question you have mentioned that you are trying to use contains in data flow. But the link you have shared is for control flow expressions.

In data flow if you have to check for substring, use instr() function.

Reference

Please try this out and let us know how it goes.

Thanks.

Answer 2

Thank you @Subashri Vasudevan , Really appreciate you took time to figure out for me.