Azure Data Factory - replace expression in derived column transformation using Mapping Data Flow

Question

Dear Team,

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.

Answer 1

Hi

Please use the below expression.

Here we are trying to replace # if any. (doesnt check if they are in leading or trailing position) and countrycode if it is equal to the country_code column.

iif(instr(original_desc_txt,country_code)>0, replace(replace(original_desc_txt,'#',''),country_code,''),original_desc_txt)

Please see below. In the second column, we see the original text before replacement. In the third column we see country code. if the second column has the country code as in third column, it will be replaced by empty string. Else the country code will be left as such along with #

Please let us know if you need any clarification