Azure Data Factory - Expressions for Strings, Dates and More

 

Summary

This blog lists some useful expressions to be used in Azure Data Factory such as manipulating and retrieving details from Strings, dates or passing and using outputs in Activities

Solution

Handling Strings 

	Scenario and Expression
1	Expression to Get the first name from Full Name FullName: Vaibhav Chaudhari Output: Vaibhav @split(pipeline().parameters.FullName, ' ') [0]
2	Expression to Get the last name from Full Name FullName: Vaibhav Chaudhari Output: Chaudhari @split(pipeline().parameters.FullName, ' ') [1]
3	Expression to Get the string between second and third slash FolderPath: Sales/Products/2019/12/10 Output: 2019 @split(pipeline().parameters.FolderPath, '/') [2]
4	Expression to Remove Spaces from a String Title: The First King Output: TheFirstKing @replace(pipeline().parameters.Title, ' ', '')
5	Expression to Pass Pipeline details in WHERE clause of a SQL Query SELECT PipelineName, Column1, Column2 FROM MyTable WHERE PipelineName = '@{pipeline().Pipeline}'
6	Expression to Append current timestamp to file name FilePath: Sales/Products/2019/12/10/product.txt Output:  Sales/Products/2019/12/10/product_2019-12-16.txt @concat(replace(pipeline().parameters.FilePath, '.txt', ''), '_', formatDateTime(utcnow(), 'yyyy-MM-dd'), '.txt')
7	Expression to remove file extension from a file name FilePath: product_2019-12-16.txt Output:  product_2019-12-16. @substring(pipeline().parameters.FileName, 0, lastindexof(pipeline().parameters.FileName, '.'))

DateTime Related

	Scenario & Expression
1	Expression to Find first Day of current Month: @startOfMonth(utcnow(), 'yyyy-MM-dd')
2	Expression to Find last day of Current Month: @adddays(startOfMonth(addToTime(utcnow(), 1, 'Month')), - 1, 'yyyy-MM-dd')
3	Expression to Find first Day of Next Month @startOfMonth(addToTime(utcnow(), 1, 'Month'), 'yyyy-MM-dd')
4	Expression to Find last day of Next Month @adddays(addToTime(startOfMonth(utcnow()), 2, 'Month'), - 1, 'yyyy-MM-dd')
5	Expression to Find first Day of Previous Month @startOfMonth(addToTime(utcnow(), - 1, 'Month'), 'yyyy-MM-dd')
6	Expression to Find last day of Previous Month: @adddays(startOfMonth(utcnow()), - 1, 'yyyy-MM-dd')