# Expression functions in mapping data flow

**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

- List of all aggregate functions.
- List of all array functions.
- List of all cached lookup functions.
- List of all conversion functions.
- List of all date and time functions.
- List of all map functions.
- List of all metafunctions.
- List of all window functions.
- Usage details of all data transformation expressions.
- Learn how to use Expression Builder.

## Feedback

https://aka.ms/ContentUserFeedback.

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for