Functions (SSIS Expressions)
The expression language includes a set of functions for use in expressions. An expression can use a single function, but typically an expression combines functions with operators and uses multiple functions.
The functions can be categorized into the following groups:
Mathematical functions that perform calculations based on numeric input values provided as parameters to the functions and return numeric values.
String functions that perform operations on string or hexadecimal input values and return a string or numeric value.
Date and time functions that perform operations on date and time values and return string, numeric, or date and time values.
System functions that return information about an expression.
The expression language provides the following mathematical functions.
Function |
Description |
|---|---|
Returns the absolute, positive value of a numeric expression. |
|
Returns the exponent to base e of the specified expression. |
|
Returns the smallest integer that is greater than or equal to a numeric expression. |
|
Returns the largest integer that is less than or equal to a numeric expression. |
|
Returns the natural logarithm of a numeric expression. |
|
Returns the base-10 logarithm of a numeric expression. |
|
Returns the result of raising a numeric expression to a power. |
|
Returns a numeric expression that is rounded to the specified length or precision. . |
|
Returns the positive (+), negative (-), or zero (0) sign of a numeric expression. |
|
Returns the square of a numeric expression. |
|
Returns the square root of a numeric expression. |
The expression evaluator provides the following string functions.
Function |
Description |
|---|---|
Returns the Unicode code value of the leftmost character of a character expression. |
|
Returns the one-based index of the specified occurrence of a character string within an expression. |
|
Returns a string representing the hexadecimal value of an integer. |
|
Returns the number of characters in a character expression. |
|
Returns a character expression after converting uppercase characters to lowercase characters. |
|
Returns a character expression after removing leading spaces. |
|
Returns a character expression after replacing a string within the expression with either a different string or an empty string. |
|
Returns a character expression, replicated a specified number of times. |
|
Returns a character expression in reverse order. |
|
Returns part of a character string starting at a specified number of characters from the right. |
|
Returns a character expression after removing trailing spaces. |
|
Returns a part of a character expression. |
|
Returns a character expression after removing leading and trailing spaces. |
|
Returns a character expression after converting lowercase characters to uppercase characters. |
The expression evaluator provides the following date and time functions.
Function |
Description |
|---|---|
Returns a new DT_DBTIMESTAMP value by adding a date or time interval to a specified date. |
|
Returns the number of date and time boundaries crossed between two specified dates. |
|
Returns an integer representing a datepart of a date. |
|
Returns an integer that represents the day of the specified date. |
|
Returns the current date of the system. |
|
Returns the current date of the system in UTC time (Universal Time Coordinate or Greenwich Mean Time). |
|
Returns an integer that represents the month of the specified date. |
|
Returns an integer that represents the year of the specified date. |
The expression evaluator provides the following null functions.
Function |
Description |
|---|---|
Returns a Boolean result based on whether an expression is null. |
|
Returns a null value of a requested data type. |
Expression names are shown in uppercase characters, but expression names are not case-sensitive. For example, using "null" works as well as using "NULL".
See Also