Tuesday, March 03, 2015
   
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:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 25-Feb-15
      SOrry ive solved the previous error but having an error context.LOCATIO N_SERVICE);
      lindalee  
    • Wed 25-Feb-15
      Hi im getting an error getLastKnownLocation(provider) can not resolve provider any hekp
      lindalee
  • JDatabase: using the Joomla database with examples

    • Sat 28-Feb-15
      High Praise, Thx Scott! I still use this for myself, nice to know it was of some use to you.
      Joel L.  
    • Fri 27-Feb-15
      Wow, thank you so much, Joomla documents are written for those who can read Joomla documents. :lol: Your ...
      Scott N.  
    • Thu 26-Feb-15
      Clearly represented jfactory querys. This helped me a lot. Thank you very much. :-)
      Oddy