Functions (SSIS)
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
Tasks
Concepts
Advanced Integration Services Expressions
Other Resources
Integration Services Expression Concepts
Operators (SSIS)