Expression functions in mapping data flow

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

Data flows are available both in Azure Data Factory and Azure Synapse Pipelines. This article applies to mapping data flows. If you are new to transformations, please refer to the introductory article Transform data using a mapping data flow.

The following articles provide details about expression functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.

Expression functions list

In Data Factory and Synapse pipelines, use the expression language of the mapping data flow feature to configure data transformations.

Expression function Task
abs Absolute value of a number.
acos Calculates a cosine inverse value.
add Adds a pair of strings or numbers. Adds a date to a number of days. Adds a duration to a timestamp. Appends one array of similar type to another. Same as the + operator.
and Logical AND operator. Same as &&.
asin Calculates an inverse sine value.
assertErrorMessages Returns map of all assert messages.
atan Calculates an inverse tangent value.
atan2 Returns the angle in radians between the positive x-axis of a plane and the point given by the coordinates.
between Checks if the first value is in between two other values inclusively. Numeric, string and datetime values can be compared
bitwiseAnd Bitwise And operator across integral types. Same as & operator.
bitwiseOr Bitwise Or operator across integral types. Same as | operator.
bitwiseXor Bitwise Or operator across integral types. Same as | operator.
blake2b Calculates the Blake2 digest of set of columns of varying primitive datatypes given a bit length. The bit length can only be multiples of 8 between 8 and 512. It can be used to calculate a fingerprint for a row.
blake2bBinary Calculates the Blake2 digest of set of column of varying primitive datatypes given a bit length, which can only be multiples of 8 between 8 & 512. It can be used to calculate a fingerprint for a row
case Based on alternating conditions applies one value or the other. If the number of inputs are even, the other is defaulted to NULL for last condition.
cbrt Calculates the cube root of a number.
ceil Returns the smallest integer not smaller than the number.
coalesce Returns the first not null value from a set of inputs. All inputs should be of the same type.
columnNames Gets the names of all output columns for a stream. You can pass an optional stream name as the first argument and optional second argument to only return schema drift columns.
columns Gets the values of all output columns for a stream. You can pass an optional stream name as the second argument.
compare Compares two values of the same type. Returns a negative integer if value1 < value2, 0 if value1 == value2, positive value if value1 > value2.
concat Concatenates a variable number of strings together. Same as the + operator with strings.
concatWS Concatenates a variable number of strings together with a separator. The first parameter is the separator.
cos Calculates a cosine value.
cosh Calculates a hyperbolic cosine of a value.
crc32 Calculates the CRC32 hash of set of column of varying primitive datatypes given a bit length. The bit length must be of values 0 (256), 224, 256, 384, or 512. It can be used to calculate a fingerprint for a row.
degrees Converts radians to degrees.
divide Divides pair of numbers. Same as the / operator.
dropLeft Removes as many characters from the left of the string. If the drop requested exceeds the length of the string, an empty string is returned.
dropRight Removes as many characters from the right of the string. If the drop requested exceeds the length of the string, an empty string is returned.
endsWith Checks if the string ends with the supplied string.
equals Comparison equals operator. Same as == operator.
equalsIgnoreCase Comparison equals operator, ignoring case. Same as <=> operator.
escape Escapes a string according to a format. Literal values for acceptable format are 'json', 'xml', 'ecmascript', 'html', 'java'.
expr Results in an expression from a string. It is equivalent to writing the expression in a non-literal form and can be used to pass parameters as string representations.
factorial Calculates the factorial of a number.
false Always returns a false value. Use the function syntax(false()) if there's a column named 'false'.
floor Returns the largest integer not greater than the number.
fromBase64 Decodes the given base64-encoded string.
greater Comparison greater operator. Same as > operator.
greaterOrEqual Comparison greater than or equal operator. Same as >= operator.
greatest Returns the greatest value among the list of values as input skipping null values. Returns null if all inputs are null.
hasColumn Checks for a column value by name in the stream. You can pass an optional stream name as the second argument. Column names known at design time should be addressed just by their name. Computed inputs aren't supported but you can use parameter substitutions.
hasError Checks if the assert with provided ID is marked as error.
iif Based on a condition applies one value or the other. If other is unspecified, it's considered NULL. Both the values must be compatible(numeric, string...).
iifNull Given two or more inputs, returns the first not null item. This function is equivalent to coalesce.
initCap Converts the first letter of every word to uppercase. Words are identified as separated by whitespace.
instr Finds the position(1 based) of the substring within a string. 0 is returned if not found.
isDelete Checks if the row is marked for delete. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isError Checks if the row is marked as error. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isIgnore Checks if the row is marked to be ignored. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isInsert Checks if the row is marked for insert. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isMatch Checks if the row is matched at lookup. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isNull Checks if the value is NULL.
isUpdate Checks if the row is marked for update. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
isUpsert Checks if the row is marked for insert. For transformations taking more than one input stream you can pass the (1-based) index of the stream. The stream index should be either 1 or 2 and the default value is 1.
jaroWinkler Gets the JaroWinkler distance between two strings.
least Comparison lesser than or equal operator. Same as <= operator.
left Extracts a substring start at index 1 with number of characters. Same as SUBSTRING(str, 1, n).
length Returns the length of the string.
lesser Comparison less operator. Same as < operator.
lesserOrEqual Comparison lesser than or equal operator. Same as <= operator.
levenshtein Gets the levenshtein distance between two strings.
like The pattern is a string that is matched literally. The exceptions are the following special symbols: _ matches any one character in the input (similar to. in posix regular expressions)
locate Finds the position(1 based) of the substring within a string starting a certain position. If the position is omitted, it's considered from the beginning of the string. 0 is returned if not found.
log Calculates log value. An optional base can be supplied else a Euler number if used.
log10 Calculates log value based on 10 base.
lower Lowercases a string.
lpad Left pads the string by the supplied padding until it is of a certain length. If the string is equal to or greater than the length, then it's trimmed to the length.
ltrim Left trims a string of leading characters. If second parameter is unspecified, it trims whitespace. Else it trims any character specified in the second parameter.
md5 Calculates the MD5 digest of set of column of varying primitive datatypes and returns a 32-character hex string. It can be used to calculate a fingerprint for a row.
minus Subtracts numbers. Subtract number of days from a date. Subtract duration from a timestamp. Subtract two timestamps to get difference in milliseconds. Same as the - operator.
mod Modulus of pair of numbers. Same as the % operator.
multiply Multiplies pair of numbers. Same as the * operator.
negate Negates a number. Turns positive numbers to negative and vice versa.
nextSequence Returns the next unique sequence. The number is consecutive only within a partition and is prefixed by the partitionId.
normalize Normalizes the string value to separate accented unicode characters.
not Logical negation operator.
notEquals Comparison not equals operator. Same as != operator.
null Returns a NULL value. Use the function syntax(null()) if there's a column named 'null'. Any operation that uses will result in a NULL.
or Logical OR operator. Same as ||.
pMod Positive Modulus of pair of numbers.
partitionId Returns the current partition ID the input row is in.
power Raises one number to the power of another.
radians Converts degrees to radians
random Returns a random number given an optional seed within a partition. The seed should be a fixed value and is used with the partitionId to produce random values
regexExtract Extract a matching substring for a given regex pattern. The last parameter identifies the match group and is defaulted to 1 if omitted. Use `<regex>` (back quote) to match a string without escaping.
regexMatch Checks if the string matches the given regex pattern. Use `<regex>` (back quote) to match a string without escaping.
regexReplace Replace all occurrences of a regex pattern with another substring in the given string Use `<regex>` (back quote) to match a string without escaping.
regexSplit Splits a string based on a delimiter based on regex and returns an array of strings.
replace Replace all occurrences of a substring with another substring in the given string. If the last parameter is omitted, it's default to empty string.
reverse Reverses a string.
right Extracts a substring with number of characters from the right. Same as SUBSTRING(str, LENGTH(str) - n, n).
rlike Checks if the string matches the given regex pattern.
round Rounds a number given an optional scale and an optional rounding mode. If the scale is omitted, it's defaulted to 0. If the mode is omitted, it's defaulted to ROUND_HALF_UP(5). The values for rounding include
rpad Right pads the string by the supplied padding until it is of a certain length. If the string is equal to or greater than the length, then it's trimmed to the length.
rtrim Right trims a string of trailing characters. If second parameter is unspecified, it trims whitespace. Else it trims any character specified in the second parameter.
sha1 Calculates the SHA-1 digest of set of column of varying primitive datatypes and returns a 40 character hex string. It can be used to calculate a fingerprint for a row.
sha2 Calculates the SHA-2 digest of set of column of varying primitive datatypes given a bit length, which can only be of values 0(256), 224, 256, 384, 512. It can be used to calculate a fingerprint for a row.
sin Calculates a sine value.
sinh Calculates a hyperbolic sine value.
soundex Gets the soundex code for the string.
split Splits a string based on a delimiter and returns an array of strings.
sqrt Calculates the square root of a number.
startsWith Checks if the string starts with the supplied string.
substring Extracts a substring of a certain length from a position. Position is 1 based. If the length is omitted, it's defaulted to end of the string.
substringIndex Extracts the substring before count occurrences of the delimiter. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
tan Calculates a tangent value.
tanh Calculates a hyperbolic tangent value.
translate Replace one set of characters by another set of characters in the string. Characters have 1 to 1 replacement.
trim Trims a string of leading and trailing characters. If second parameter is unspecified, it trims whitespace. Else it trims any character specified in the second parameter.
true Always returns a true value. Use the function syntax(true()) if there's a column named 'true'.
typeMatch Matches the type of the column. Can only be used in pattern expressions.number matches short, integer, long, double, float or decimal, integral matches short, integer, long, fractional matches double, float, decimal and datetime matches date or timestamp type.
unescape Unescapes a string according to a format. Literal values for acceptable format are 'json', 'xml', 'ecmascript', 'html', 'java'.
upper Uppercases a string.
uuid Returns the generated UUID.
xor Logical XOR operator. Same as ^ operator.

Related content