Print

Trim in T-SQL and SSIS

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

How?
First in Transact-SQL:
copyraw
SELECT 
     RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') )

-- CHAR(9) = Tab
-- CHAR(10) = Line Feed
-- CHAR(13) = Carriage Return
  1.  SELECT 
  2.       RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') ) 
  3.   
  4.  -- CHAR(9) = Tab 
  5.  -- CHAR(10) = Line Feed 
  6.  -- CHAR(13) = Carriage Return 

And in SSIS:
copyraw
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", 
			""
		)
	)
)
  1.  TRIM( LOWER( REPLACE( REPLACE( REPLACE( myString, "\x0009", "" ), "\x000A", "" ), "\x000D", "" ) ) ) 
  2.   
  3.  -- \x0009 = Tab 
  4.  -- \x000A = Line Feed 
  5.  -- \x000D = Carriage Return 
  6.   
  7.  -- or split for clarity 
  8.  TRIM( 
  9.      LOWER( 
  10.          REPLACE( 
  11.              REPLACE( 
  12.                  REPLACE( 
  13.                      myString, 
  14.                      "\x0009", 
  15.                      "" 
  16.                  ), 
  17.                  "\x000A", 
  18.                  "" 
  19.              ), 
  20.              "\x000D", 
  21.              "" 
  22.          ) 
  23.      ) 
  24.  ) 


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:
copyraw
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
  1.  Table: DMExtractEmployeeTable 
  2.  ID     ForeName      SurName 
  3.  --------------------------------- 
  4.  1      Joel          Lipman 
  5.  2      JOE           THE GREATEST 
  6.   
  7.   
  8.   
  9.  DECLARE @EmployeeStr = 'JOE ' + CHAR(9) + CHAR(10) + CHAR(13) 
  10.   
  11.   
  12.  -- Before 
  13.  SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = 'JOE' 
  14.  -- yields 1 
  15.   
  16.   
  17.  -- What we want (equivalent?) 
  18.  SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = @EmployeeStr 
  19.  -- yields 0 
  20.   
  21.   
  22.  -- After (replacing the = with LIKE) 
  23.  SELECT COUNT(*) FROM Employees WHERE Employee.ForeName LIKE @EmployeeStr 
  24.  -- yields 1 


Category: SQL Server Integration Services :: Article: 509