none
Trying to Remove White Space from a field

    Question

  • In SSIS I have a table with a field named StreetNbr and a field named Street, I am attempting to concatenate these to fields into a new field named Address.  When concatenating the fields together I get 2 blank spaces between StreetNbr and Street.  (I'm not having any problems concatenating them)

    StreetNBR --> varchar (8) 
    Street --> varchar (40)

    What I've done:
    1) Within the Data Flow Task I added a Derived Column
    2) Inside the Derived Column I wrote the expression streetnbr+street

    This concatenates the informaiton, however there are two empty spaces between streetnbr and street

    Wednesday, July 01, 2009 3:03 PM

Answers

  • In the derived column TRIM both columns.
    RTRIM(LTRIM( [streetnbr] ) ) + RTRIM(LTRIM( [street] ) )
    Hope thsi helps
    David Dye
    • Marked as answer by SB79 Wednesday, July 01, 2009 6:09 PM
    Wednesday, July 01, 2009 3:15 PM
  • To add to what David said, to include a single empty space, you'd want to use:

    RTRIM(LTRIM( [streetnbr] ) ) + " " + RTRIM(LTRIM( [street] ) )
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Wednesday, July 01, 2009 3:38 PM

All replies

  • In the derived column TRIM both columns.
    RTRIM(LTRIM( [streetnbr] ) ) + RTRIM(LTRIM( [street] ) )
    Hope thsi helps
    David Dye
    • Marked as answer by SB79 Wednesday, July 01, 2009 6:09 PM
    Wednesday, July 01, 2009 3:15 PM
  • To add to what David said, to include a single empty space, you'd want to use:

    RTRIM(LTRIM( [streetnbr] ) ) + " " + RTRIM(LTRIM( [street] ) )
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Wednesday, July 01, 2009 3:38 PM
  • It Worked THANK YOU!!
    Wednesday, July 01, 2009 6:09 PM