locked
Trim white space IN a string, not ltrim/rtrim RRS feed

  • Question

  • We connect to an IBM Mainframe and receive database dumps via DB2 that is shot over to SQL. The problem is white space when there is multiple lines in a comments field per say. Imagine that you have 5 rows that have a fixed length of 20 characters. You only fill up 15 characters with actual text, the other 5 characters become white space. Then the next line is concatted with 5 characters of white space which is now in the middle of string, not at either end.

    This is cannot be changed due to corporate policy of how data is pulled down since we are a division of the company. My question, how could i trim whitespace which is greater than 2-3 characters of white space at the SQL level?

    Friday, August 28, 2009 1:19 PM

Answers

  • How about something like this:

    declare @string as varchar(40)
    Set @string = 'this is       an example'
    Set @string = REPLACE(@string,'   ',' ')
    Set @string = REPLACE(@string,'  ',' ')
    Select @string
    
    Just progressively go through the string looking for white space that is either 2 or 3 chars in length. You'll have to test this of course...but it may work for you.

    JP
    Please click the Mark as Answer button if a post solves your problem!
    http://www.johnvpetersen.com http://www.cognisit.com
    • Marked as answer by Zongqing Li Thursday, September 3, 2009 6:14 AM
    Friday, August 28, 2009 1:28 PM

All replies

  • Hi, you could use the Replace function.


    Replace('YourString','String to be replaced','replacementString')

    So in your case it would be:

    REPLACE(YourString,' ','') this would clear all whitespaces.

    Hope to help

    Friday, August 28, 2009 1:24 PM
  • for just 2 or 3 chars, you can use replace() twice
    replace(replace(col, '   ', ' '), '  ', ' ')

    KH Tan
    Friday, August 28, 2009 1:25 PM
  • How about something like this:

    declare @string as varchar(40)
    Set @string = 'this is       an example'
    Set @string = REPLACE(@string,'   ',' ')
    Set @string = REPLACE(@string,'  ',' ')
    Select @string
    
    Just progressively go through the string looking for white space that is either 2 or 3 chars in length. You'll have to test this of course...but it may work for you.

    JP
    Please click the Mark as Answer button if a post solves your problem!
    http://www.johnvpetersen.com http://www.cognisit.com
    • Marked as answer by Zongqing Li Thursday, September 3, 2009 6:14 AM
    Friday, August 28, 2009 1:28 PM
  • If there is a character that is not being used, Peso's REPLACE idea can be used: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/200027c8-a637-4c53-833c-88ee0e4c5074/
    • Edited by Brian Tkatch Friday, August 28, 2009 3:28 PM linky linky
    Friday, August 28, 2009 1:44 PM
  • Thank you everyone! the replace function worked great! Appericate the help! It didnt cross my mind to use replace ::doh!::
    Friday, August 28, 2009 1:51 PM
  • No problem..glad to be of help... Please remember to mark the replies as answers so that when somebody else has this issue, this thread can be of use to those folks as well. :)

    JP
    Please click the Mark as Answer button if a post solves your problem!
    http://www.johnvpetersen.com http://www.cognisit.com
    Friday, August 28, 2009 1:54 PM