Trim in T-SQL and SSIS

: Category: SQL Server Integration Services; 14 May 2013; Hits: 10071

What?
Some methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines).

How?
First in Transact-SQL:

copy raw

SELECT 
     RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') )

-- CHAR(9) = Tab
-- CHAR(10) = Line Feed
-- CHAR(13) = Carriage Return

SELECT
RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') )
-- CHAR(9) = Tab
-- CHAR(10) = Line Feed
-- CHAR(13) = Carriage Return

And in SSIS:

copy raw

TRIM( LOWER( REPLACE( REPLACE( REPLACE( myString, "\x0009", "" ), "\x000A", "" ), "\x000D", "" ) ) ) 

-- \x0009 = Tab
-- \x000A = Line Feed
-- \x000D = Carriage Return

-- or split for clarity
TRIM(
	LOWER(
		REPLACE(
			REPLACE(
				REPLACE(
					myString, 
					"\x0009", 
					""
				), 
				"\x000A", 
				""
			), 
			"\x000D", 
			""
		)
	)
)

TRIM( LOWER( REPLACE( REPLACE( REPLACE( myString, "\x0009", "" ), "\x000A", "" ), "\x000D", "" ) ) )
-- \x0009 = Tab
-- \x000A = Line Feed
-- \x000D = Carriage Return
-- or split for clarity
TRIM(
LOWER(
REPLACE(
REPLACE(
REPLACE(
myString,
"\x0009",
""
),
"\x000A",
""
),
"\x000D",
""
)
)
)

Using it to compare to a value?
There are cases in T-SQL where the trim is unecessary and a similar equation can be written:

copy raw

Table: DMExtractEmployeeTable
ID     ForeName      SurName
---------------------------------
1      Joel          Lipman
2      JOE           THE GREATEST



DECLARE @EmployeeStr = 'JOE ' + CHAR(9) + CHAR(10) + CHAR(13)


-- Before
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = 'JOE'
-- yields 1


-- What we want (equivalent?)
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = @EmployeeStr
-- yields 0


-- After (replacing the = with LIKE)
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName LIKE @EmployeeStr
-- yields 1