How to select characters between wildcards in Azure Data Factory expression for Switch Block

Question

I have 2 pipelines that are currently selected based on an IF condition, which works well. I now need to add a third pipeline , so using a Switch block instead. And instead of tablename as the deciding factor, ideally I would use logic something like this:

1. does the sourcetablename value contain {FromDate} then goto pipeline A
2. does the sourcetablename value contain {passinid} then goto pipeline B
3. else goto pipeline C

The sourcetablename value comes with the initial gettablestoload lookup.

An example sourcetablename value is used for the API call in this instance, and would look like this for example: blahblah/content/{passinid}/user/blahblah

I am struggling with the expression for the switchblock. Previously I have matched on the last 10 characters of the tablename, this just seems a little bit tricky.

Here is an example of an expression to remove the last 46 characters, just to give you an idea where I am struggling up to:

@substring(activity('GetTablesToLoad').SourceTableName,0,sub(length(activity('GetTablesToLoad').SourceTableName),46))

Would anyone have an idea please?

If this was SQL it would be something like this:

 DECLARE @text VARCHAR (500) = 'content/{passinid}/user'
    SELECT SUBSTRING(@Text, CHARINDEX('{', @Text)
, CHARINDEX('}',@text) - CHARINDEX('{', @Text) + Len('}'))

thankyou

Answer 1

You can use if function to check for required value directly. I have taken a parameter called sourceTableName for demonstration instead of lookup value.

• I have used the following dynamic content as expression value for switch activity. You can replace the pipeline().parameters.sourceTableName with lookup activity SourceTableName value. The name of switch case cannot start with { so, I have directly taken the name.

@if(contains(pipeline().parameters.sourceTableName,'{passinid}'),'passinid',if(contains(pipeline().parameters.sourceTableName,'{FromDate}'),'FromDate','execute default'))

• When the parameter value is blahblah/content/{passinid}/user/blahblah, the corresponding set variable3 is executed.

• When the parameter value is blahblah/content/{FromDate}/user/blahblah, the corresponding set variable4 is executed.

• If these values are not present (blahblah/conten/user/blahblah), then the default case would be executed (set variable2 activity).