Azure Data Factory - replace expression in derived column transformation using Mapping Data Flow
I am trying to use Azure Data Factory for a Derived column transformation task one of my tasks is mentioned below,
DESCRIPTION_TEXT : UNILEVER GROUP ##### GBR
Remove trailing country code (only when it equals to country) and ##### if they exist
My code in Expression builder:
replace(ORIGINAL_DESCRIPTION_TEXT, COUNTRY_CODE, substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1))
and this is not working as I expected
(Out put is : UNILEVER GROUP ##### UNILEVER GROUP )
The Expected output is UNILEVER GROUP.
Please help me to solve this problem.
I have reproduced the above and able to achieve your requirement using iif in dataflow expression like below.
iif(and(greater(instr(ORIGINAL_DESCRIPTION_TEXT,'#'),0),greater(instr(ORIGINAL_DESCRIPTION_TEXT, $country_code),0)), substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1), ORIGINAL_DESCRIPTION_TEXT)
Here country_code is a parameter and I have given the values as 'GBR'.
If the ORIGINAL_DESCRIPTION_TEXT column contains '#' and country_codey, then it stores the string from start to the index of '#'. If not, it stores the same column without any changes.
Result:
