• Article

String Functions and Other Functions (SSIS Expression)

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.

Mathematical Functions

The expression language provides the following mathematical functions.

Function

Description

ABS (SSIS Expression)

Returns the absolute, positive value of a numeric expression.

EXP (SSIS Expression)

Returns the exponent to base e of the specified expression.

CEILING (SSIS Expression)

Returns the smallest integer that is greater than or equal to a numeric expression.

FLOOR (SSIS Expression)

Returns the largest integer that is less than or equal to a numeric expression.

LN (SSIS Expression)

Returns the natural logarithm of a numeric expression.

LOG (SSIS Expression)

Returns the base-10 logarithm of a numeric expression.

POWER (SSIS Expression)

Returns the result of raising a numeric expression to a power.

ROUND (SSIS Expression)

Returns a numeric expression that is rounded to the specified length or precision. .

SIGN (SSIS Expression)

Returns the positive (+), negative (-), or zero (0) sign of a numeric expression.

SQUARE (SSIS Expression)

Returns the square of a numeric expression.

SQRT (SSIS Expression)

Returns the square root of a numeric expression.

SSIS String Functions

The expression evaluator provides the following string functions.

Function

Description

CODEPOINT (SSIS Expression)

Returns the Unicode code value of the leftmost character of a character expression.

FINDSTRING (SSIS Expression)

Returns the one-based index of the specified occurrence of a character string within an expression.

HEX (SSIS Expression)

Returns a string representing the hexadecimal value of an integer.

LEN (SSIS Expression)

Returns the number of characters in a character expression.

LOWER (SSIS Expression)

Returns a character expression after converting uppercase characters to lowercase characters.

LTRIM (SSIS Expression)

Returns a character expression after removing leading spaces.

REPLACE (SSIS Expression)

Returns a character expression after replacing a string within the expression with either a different string or an empty string.

REPLICATE (SSIS Expression)

Returns a character expression, replicated a specified number of times.

REVERSE (SSIS Expression)

Returns a character expression in reverse order.

RIGHT (SSIS Expression)

Returns part of a character string starting at a specified number of characters from the right.

RTRIM (SSIS Expression)

Returns a character expression after removing trailing spaces.

SUBSTRING (SSIS Expression)

Returns a part of a character expression.

TRIM (SSIS Expression)

Returns a character expression after removing leading and trailing spaces.

UPPER (SSIS Expression)

Returns a character expression after converting lowercase characters to uppercase characters.

Date and Time Functions

The expression evaluator provides the following date and time functions.

Function

Description

DATEADD (SSIS Expression)

Returns a new DT_DBTIMESTAMP value by adding a date or time interval to a specified date.

DATEDIFF (SSIS Expression)

Returns the number of date and time boundaries crossed between two specified dates.

DATEPART (SSIS Expression)

Returns an integer representing a datepart of a date.

DAY (SSIS Expression)

Returns an integer that represents the day of the specified date.

GETDATE (SSIS Expression)

Returns the current date of the system.

GETUTCDATE (SSIS Expression)

Returns the current date of the system in UTC time (Universal Time Coordinate or Greenwich Mean Time).

MONTH (SSIS Expression)

Returns an integer that represents the month of the specified date.

YEAR (SSIS Expression)

Returns an integer that represents the year of the specified date.

NULL Functions

The expression evaluator provides the following null functions.

Function

Description

ISNULL (SSIS Expression)

Returns a Boolean result based on whether an expression is null.

NULL (SSIS Expression)

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".

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.