TRIM (SSIS Expression)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns a character expression after removing leading and trailing spaces.
TRIM does not remove white-space characters such as the tab or line feed characters. Unicode provides code points for many different types of spaces, but this function recognizes only the Unicode code point 0x0020. When double-byte character set (DBCS) strings are converted to Unicode they may include space characters other than 0x0020 and the function cannot remove such spaces. To remove all kinds of spaces, you can use the Microsoft Visual Basic .NET Trim method in a script run from the Script component.
TRIM returns a null result if the argument is null.
TRIM works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before TRIM performs its operation. Other data types must be explicitly cast to the DT_WSTR data type. For more information, see Integration Services Data Types and Cast (SSIS Expression).
This example removes leading and trailing spaces from a string literal. The return result is "New York".
TRIM(" New York ")
This example removes leading and trailing spaces from the result of concatenating the FirstName and LastName columns. The empty string between FirstName and LastName is not removed.
TRIM(FirstName + " "+ LastName)