Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    54

    Unanswered: DB2 : How to eliminate extra spaces in VARCHAR columns ?

    Using PUTTY or CMD console when I tried to save data in text file it save data with white space of size of column even data is very less then column size.

    DB2 Version: 9.5
    Accessing data: PUTTY or CMD console
    Data type: Varchar(100)
    Actual length: 20
    Command:
    DB2 “select name from emp” >test.txt

    I have tried all trim function but no help and I do not want to use cast function due to many number of select column in table.

    Please help me to give some dynamic solution to handle all select columns without any white space.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Use export

    export to test.txt of del select name from emp

    this will write data to a csv file.

    Please note, this will not have column names in the header.

    If you do not want the " around the name, you can do

    export to test.txt of del modified by nochardel select name from emp

    HTH

    Sathyaram

    PS: if you get a syntax error when using the commands, check the manuals for the syntax.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2011
    Posts
    54
    Thanks Sathyaram,
    I am aware about export solution but am looking some solution for truncate white space on console save data. There might be something on Unix or CMD on that we can set page and column variable for session to display page and according save data in text file.

  4. #4
    Join Date
    Apr 2012
    Posts
    4
    Hi
    I have the same issue... have you found a solution ?

    Ty

  5. #5
    Join Date
    Nov 2012
    Posts
    10

    Eliminating padded blanks / trailing spaces in a DB2 VARCHAR column

    Quote Originally Posted by rmazzola View Post
    Hi
    I have the same issue... have you found a solution ?

    Ty
    Hello,

    Looks like it has been a while and not sure if anyone posted a solution. I did not try this with earlier DB2 versions but with DB2 V11, if you just use the CHAR function on a VARCHAR column it returns the values by automatically eliminating the padded blanks / trailing spaces. You may have already found this but wanted to post it again for the benefit of others.

    When tried this query "SELECT BUS_PROCESS FROM Prefix.Busisness" on a VARCHAR(50) column BUS_PROCESS it returned 'SE Payments.......................................'

    But when I tried ""SELECT CHAR(BUS_PROCESS) FROM Prefix.Busisness" it returned 'SE Payments'. No padded blanks.

    If you are already aware of this, my apologies.

    Suren

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •