Reputation: 113

Data Factory expression substring? Is there a function similar like right?

Please help,

How could I extract 2019-04-02 out of the following string with Azure data flow expression?

ABC_DATASET-2019-04-02T02:10:03.5249248Z.parquet

The first part of the string received as a ChildItem from a GetMetaData activity is dynamically. So in this case it is ABC_DATASET that is dynamic.

Kind regards,

Upvotes: 2

Answers (1)

Joel Cochran

Reputation: 7728

There are several ways to approach this problem, and they are really dependent on the format of the string value. Each of these approaches uses Derived Column to either create a new column or replace the existing column's value in the Data Flow.

Static format

If the format is always the same, meaning the length of the sections is always the same, then substring is simplest:

This will parse the string like so:

Useful reminder: substring and array indexes in Data Flow are 1-based.

Dynamic format

If the format of the base string is dynamic, things get a tad trickier. For this answer, I will assume that the basic format of {variabledata}-{timestamp}.parquet is consistent, so we can use the hyphen as a base delineator.

Derived Column has support for local variables, which is really useful when solving problems like this one. Let's start by creating a local variable to convert the string into an array based on the hyphen. This will lead to some other problems later since the string includes multiple hyphens thanks to the timestamp data, but we'll deal with that later. Inside the Derived Column Expression Builder, select "Locals":

On the right side, click "New" to create a local variable. We'll name it and define it using a split expression:

Press "OK" to save the local and go back to the Derived Column. Next, create another local variable for the yyyy portion of the date:

The cool part of this is I am now referencing the local variable array that I created in the previous step. I'll follow this pattern to create a local variable for MM too:

I'll do this one more time for the dd portion, but this time I have to do a bit more to get rid of all the extraneous data at the end of the string. Substring again turns out to be a good solution:

Now that I have the components I need isolated as variables, we just reconstruct them using string interpolation in the Derived Column:

Back in our data preview, we can see the results:

Where else to go from here

If these solutions don't address your problem, then you have to get creative. Here are some other functions that may help:

Upvotes: 2

Data Factory expression substring? Is there a function similar like right?

Answers (1)

Related Questions