Saturday, December 20, 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

  • Basic Android App using Google Maps and Current Location

    • Wed 17-Dec-14
      Is that how it's spelt? setContentVie( "View". If not try going to build and "clean project". Also ...
      Joel L.  
    • Tue 16-Dec-14
      I have problem in "cannot resolved R" " setContentVie(R .layout.activit y_maps);" :cry: How to solve ...
      LucasR
  • Migrate Joomla! 1.5.x to 2.5.x+

    • Thu 18-Dec-14
      Interesting report LLiSEIL! I'll definitely have to review it when Joomla 3.5 comes out. Error 1: is ...
      Joel L.  
    • Tue 16-Dec-14
      * Categories: - duplicates, I suppose from the multiple times I launched the script. - love the notes ...
      LLiSEIL  
    • Tue 16-Dec-14
      SQL ERror 3 > -- Step 2) Import articles from J15 and insert as articles in J30 INSERT INTO `newbase_j25`.d ...
      LLiSEIL