like function - Dataflow expression builder - Azure data factory

Question

Hi,

I have a file (Say name is XYZ_TX1_07_09_2022) in which I have a column 'Title' with multiple entries. Using this Title I'm trying to achieve below but the issue I'm facing is I can't pass dynamic value to the second position of like function.

-----Inside the file-----
Title
Vivek 07 09 2022
Anirudh 0709 2022
Victor 07092022
Hari 0709 2022 Krishna

Doesn't work -> like(replace(Title, ' ', ''),concat(substring($FileName,9,2),substring($FileName,12,2),substring($FileName,17,2)))
Doesn't work -> like(replace(Title, ' ', ''),concat(''%',substring($FileName,9,2),substring($FileName,12,2),substring($FileName,17,2),'%''))

I'm trying to check if value contains the date, if yes then I would like to do something.

Answer 1

Hi @Vivek Komarla Bhaskar ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As I understand your ask, you are trying to compare the filename against the column values and want to fetch out all the rows which matches the date present in the filename. Please let me know if my understanding is incorrect.

As I can see your expression , you are trying to make use of like function , however, it doesn't seem to work that way as we can't concat column name with '%' . Either we can get the exact substring value for both 'Title' and 'FileName' and use 'Like'/'Equals' function or we can use Regular expression to achieve this requirement.

You can go for one of the below workarounds:

1. Using equals function :

equals(substring(replace(Title, ' ', ''),locate('0',replace(Title, ' ', '')),8),concat(substring($FileName,9,2),substring($FileName,12,2),substring($FileName,15,4)))  

2. Using like function :

like(substring(replace(Title, ' ', ''),locate('0',replace(Title, ' ', '')),8),concat(substring($FileName,9,2),substring($FileName,12,2),substring($FileName,15,4)))  

3. Using Regex expression:

regexMatch(replace(Title, ' ', ''), '^*.'+concat(substring($FileName,9,2),substring($FileName,12,2),substring($FileName,15,4))+'.*$')  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators