Edit

Share via

Expression functions in mapping data flows

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 in both Azure Data Factory pipelines and Azure Synapse Analytics pipelines. This article applies to mapping data flows. If you're new to transformations, refer to the introductory article Transform data using mapping data flows.

This article provides details about expression functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.

Expression functions list

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

Expression function Task
abs Gives the 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 time stamp. Appends one array of similar type to another. Same as the + operator.
and Uses the logical AND operator. Same as &&.
asin Calculates an inverse sine value.
assertErrorMessages Returns a 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. You can compare numeric, string, and datetime values.
bitwiseAnd Uses the bitwise And operator across integral types. Same as the & operator.
bitwiseOr Uses the bitwise Or operator across integral types. Same as the | operator.
bitwiseXor Uses the bitwise Or operator across integral types. Same as the | operator.
blake2b Calculates the Blake2 digest of a set of columns of varying primitive data types when given a bit length. The bit length can only be multiples of 8 between 8 and 512. You can use it to calculate a fingerprint for a row.
blake2bBinary Calculates the Blake2 digest of a set of columns of varying primitive data types when given a bit length, which can only be multiples of 8 between 8 and 512. You can use it to calculate a fingerprint for a row.
case Applies one value or the other based on alternating conditions. If the number of inputs is even, the other is defaulted to NULL for the 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 return only 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 or 0 if value1 == value2, and returns a 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 a set of columns of varying primitive data types when given a bit length. The bit length must be of values 0 (256), 224, 256, 384, and 512. You can use it to calculate a fingerprint for a row.
degrees Converts radians to degrees.
divide Divides a pair of numbers. Same as the / operator.
dropLeft Removes as many characters as specified 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 as specified 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 Uses the comparison equals operator. Same as the == operator.
equalsIgnoreCase Uses the comparison equals operator and ignores case. Same as the <=> operator.
escape Escapes a string according to a format. Literal values for acceptable format are json, xml, ecmascript, html, and java.
expr Results in an expression from a string. Equivalent to writing the expression in a nonliteral form. You can use it 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 specific base64-encoded string.
greater Uses the comparison greater than operator. Same as the > operator.
greaterOrEqual Uses the comparison greater than or equal to operator. Same as the >= 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 by using their name only. Computed inputs aren't supported, but you can use parameter substitutions.
hasError Checks if the assert with the provided ID is marked as an error.
iif Applies one value or the other based on a condition. If the other is unspecified, the value is considered NULL. Both the values must be compatible (for example, numeric or string).
iifNull Returns the first not null item when given two or more inputs. 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. If not found, 0 is returned.
isDelete Checks if the row is marked for delete. For transformations that take 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 that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
isIgnore Checks if the row should be ignored. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
isInsert Checks if the row is marked for insert. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
isMatch Checks if the row is matched at lookup. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
isNull Checks if the value is NULL.
isUpdate Checks if the row is marked for update. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
isUpsert Checks if the row is marked for insert. For transformations that take more than one input stream, you can pass the (1-based) index of the stream. The stream index should be either 1 or 2. The default value is 1.
jaroWinkler Gets the JaroWinkler distance between two strings.
least Uses the comparison lesser than or equal to operator. Same as the <= operator.
left Extracts a substring start at index 1 with the number of characters. Same as SUBSTRING(str, 1, n).
length Returns the length of the string.
lesser Uses the comparison less operator. Same as the < operator.
lesserOrEqual Uses the comparison lesser than or equal to operator. Same as the <= operator.
levenshtein Gets the levenshtein distance between two strings.
like Uses a pattern with a string that's matched literally. The exception is the special symbol _ that 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 that starts a certain position. If the position is omitted, start from the beginning of the string. If not found, 0 is returned.
log Calculates the log value. You can supply an optional base or else a Euler number if used.
log10 Calculates the log value based on a 10 base.
lower Lowercases a string.
lpad Left pads the string by the supplied padding until it's of a certain length. If the string is equal to or greater than the length, the string is trimmed to the length.
ltrim Left trims a string of leading characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter.
md5 Calculates the MD5 digest of a set of columns of varying primitive data types and returns a 32-character hex string. You can use it to calculate a fingerprint for a row.
minus Subtracts numbers. Subtracts the number of days from a date. Subtracts duration from a time stamp. Subtracts two time stamps to get the difference in milliseconds. Same as the - operator.
mod Gives the modulus of a pair of numbers. Same as the % operator.
multiply Multiplies a 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 partition ID.
normalize Normalizes the string value to separate accented Unicode characters.
not Uses the logical negation operator.
notEquals Uses the comparison not equals operator. Same as the != operator.
null Returns a NULL value. Use the function syntax(null()) if there's a column named null. Any operation that uses it results in a NULL.
or Uses the logical OR operator. Same as ||.
pMod Gives the positive modulus of a pair of numbers.
partitionId Returns the current partition ID that the input row is in.
power Raises one number to the power of another.
radians Converts degrees to radians.
random Returns a random number when given an optional seed within a partition. The seed should be a fixed value and is used with the partition ID to produce random values.
regexExtract Extracts a matching substring for a specific regex pattern. The last parameter identifies the match group and defaults to 1 if omitted. Use `<regex>` to match a string without escaping.
regexMatch Checks if the string matches the specific regex pattern. Use `<regex>` to match a string without escaping.
regexReplace Replaces all occurrences of a regex pattern with another substring in the specific string. Use `<regex>` to match a string without escaping.
regexSplit Splits a string based on a delimiter based on regex and returns an array of strings.
replace Replaces all occurrences of a substring with another substring in the specific string. If the last parameter is omitted, it defaults to an empty string.
reverse Reverses a string.
right Extracts a substring with the number of characters from the right. Same as SUBSTRING(str, LENGTH(str) - n, n).
rlike Checks if the string matches the specific regex pattern.
round Rounds a number when given an optional scale and an optional rounding mode. If the scale is omitted, it defaults to 0. If the mode is omitted, it defaults to ROUND_HALF_UP(5).
rpad Right pads the string by the supplied padding until it's of a certain length. If the string is equal to or greater than the length, the string is trimmed to the length.
rtrim Right trims a string of trailing characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter.
sha1 Calculates the SHA-1 digest of a set of columns of varying primitive data types and returns a 40-character hex string. You can use it to calculate a fingerprint for a row.
sha2 Calculates the SHA-2 digest of a set of columns of varying primitive data types when given a bit length, which can only be of values 0 (256), 224, 256, 384, and 512. You can use it 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 defaults to the 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 Replaces one set of characters by another set of characters in the string. Characters have one-to-one replacement.
trim Trims a string of leading and trailing characters. If the second parameter is unspecified, it trims whitespace. Otherwise, it trims any character specified in the second parameter.
true Always returns a true value. Use the function syntax(true()) if a column is named true.
typeMatch Matches the type of the column. Used in pattern expressions only. Number matches short, integer, long, double, float, or decimal. Integral matches short, integer, and long. Fractional matches double, float, and decimal. Datetime matches the date or time stamp type.
unescape Unescapes a string according to a format. Literal values for acceptable format are json, xml, ecmascript, html, and java.
upper Uppercases a string.
uuid Returns the generated UUID.
xor Gives the logical XOR operator. Same as the ^ operator.

Related content

  • Last updated on