Tuesday, October 21, 2014
   
Text Size
Login

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:

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

And in SSIS:

  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:

  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 


Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Convert to Proper Case in T-SQL

    • Tue 07-Oct-14
      Really comprehensive function. Nice work! Dave.
      starsky51
  • Joes Quicklist Weblinks (JQW)

    • Wed 08-Oct-14
      Thanks jazzmang! This module is due for an update so I'll review this issue in the next version.
      Webmaster  
    • Tue 07-Oct-14
      I've set "Display Images" under Module Settings but the modules continues to display a hard coded CSS ...
      jazzmang
  • Joes Word Cloud (JWC)

    • Thu 16-Oct-14
      hello i use your tag cloud component in my joomla 2.5 website. i'm french and there is probleme with ...
      plykite2010  
    • Tue 07-Oct-14
      Hello, i have Joomla 2.5.27 and Module JWC v3.2.2 installed. In Configuration i fill in how many Word ...
      Maic