Wednesday, July 30, 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

  • Joes Quicklist Weblinks (JQW)

    • Thu 17-Jul-14
      Hi, thanks a lot. But I have got a problem with download it. Error: "File could not be found Important!
      Kat  
    • Tue 15-Jul-14
      Hi Artur, I forgot to get back to you sooner. I was going to include a set of instructions to do this ...
      Webmaster  
    • Mon 14-Jul-14
      Hi Skarosg, Not sure if we took this to e-mail but I noticed your site is displaying the images now.
      Webmaster  
    • Fri 11-Jul-14
      Hi, How can I put user name and category of weblink in module, near the date? Thanks..
      Artur
  • Joes Word Cloud (JWC)

    • Tue 17-Jun-14
      I'm using JE 3.1.19 stable.
      alve89