How to use replace function in a query with the concat function in ADF pipeline expression builder

Question

I am trying to run an incremental load and have been using the concat function with if statements, however I want to replace the '' character in the column values of a single function

I tried to use the replace function and formulated my query like this:

@if(contains(activity('ADS_Lookup').output,'firstRow'),concat('select ID,DESCRIPTION,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,IS_DEFAULT,EXTERNAL_CODE,DISCONTINUE_DATE,RESERVED_VALUE_FLAG,DEVELOPER_DESC,SORT_ORDER,SYSTEM_MODULE_ID,ACTION_ID,PARAMETER_TYPE,MODIFIABLE_BY_BC, Replace(VALUE,'/','') as VALUE,EXT_DATA from [dbo].[T_APPLICATION_PARAMS] where update_date >=''',activity('ADS_Lookup').output.firstRow.startTime,''''),concat('select ID,DESCRIPTION,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,IS_DEFAULT,EXTERNAL_CODE,DISCONTINUE_DATE,RESERVED_VALUE_FLAG,DEVELOPER_DESC,SORT_ORDER,SYSTEM_MODULE_ID,ACTION_ID,PARAMETER_TYPE,MODIFIABLE_BY_BC,Replace(VALUE,'/','') as VALUE,EXT_DATA from[dbo].[T_APPLICATION_PARAMS]'))

The query is throwing an error

Answer 1

In your dynamic expression you need to escape single quotes to get appropriate query. you can do that like this ''''

use below expression:

@if(contains(activity('ADS_Lookup').output,'firstRow'),concat('select ID,DESCRIPTION,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,IS_DEFAULT,EXTERNAL_CODE,DISCONTINUE_DATE,RESERVED_VALUE_FLAG,DEVELOPER_DESC,SORT_ORDER,SYSTEM_MODULE_ID,ACTION_ID,PARAMETER_TYPE,MODIFIABLE_BY_BC, Replace(VALUE,''/'','''') as VALUE,EXT_DATA from [dbo].[T_APPLICATION_PARAMS] where update_date >=''',activity('ADS_Lookup').output.firstRow.Id,''''),concat('select ID,DESCRIPTION,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,IS_DEFAULT,EXTERNAL_CODE,DISCONTINUE_DATE,RESERVED_VALUE_FLAG,DEVELOPER_DESC,SORT_ORDER,SYSTEM_MODULE_ID,ACTION_ID,PARAMETER_TYPE,MODIFIABLE_BY_BC,Replace(VALUE,''/'','''') as VALUE,EXT_DATA from[dbo].[T_APPLICATION_PARAMS]'))

Output: