how to truncate unused spaces in a column during select

roopannamdhari · New User Joined: 14 Sep 2006 Posts: 71 Location: Bangalore

Hi,
I am having one table1 .

table1
column1 - char(40)
column2 - char(40)

column1 column2
abcd efh
abcd efh

On execution of following query I am getting output as

Select * from table1

OUTPUT:
column1 column2
abcd(36 spaces) efh(37 spaces)

Please let me know how to truncate unused spaces in a column during select statement.

Thanks,

murmohk1 · Posted: Tue Jul 15, 2008 12:29 pm

Roopa,

Try STRIPing the column.

dbzTHEdinosauer · Posted: Tue Jul 15, 2008 12:43 pm

That is all well and good, but what is the destination field for the column?

you will have to have a host variable large enough to receive the value returned by the select. I doubt that all values contained in these two columns (bytes > spaces) are of the same length. So, eventhough you remove spaces, you will have them again in the host varibale. These columns do not appear to be defined as varchar.

performing the db2 strip is a waste of time. What is your final destination field? Unless you are using PL/1, C or Java as your application, COBOL does not lend itself to variable length fields.

what actually do you need to do?

roopannamdhari · New User Joined: 14 Sep 2006 Posts: 71 Location: Bangalore

Hi Murali,
Can u please breif me regarding Striping THE COLUMN

Thanks,

enrico-sorichetti · Posted: Tue Jul 15, 2008 2:46 pm

as a general point there is no reason to blank_strip a variable
an area large enough to contain the whole string must be provided anyway

th only reasonable ( but as usual it depends ) alternative is to use, IIRC, LTRIM to left align things ( for a quick display )

but to give the best answer the whole requirements must be clear

dick scherrer · Posted: Tue Jul 15, 2008 8:04 pm

Hello,

It would be better to focus on the replies that may help with your requirement than the replies that sound like what you believe you want to hear. . .

As has been repeated, STRIP will accomplish little as you still need a variable of proper length. . .

As has also been mentioned, if you explain your requirement (rather than some possible solution), someone here may have suggestions.

stodolas · Posted: Wed Jul 16, 2008 9:45 am

Why is there an assumption the output is in a program and has a destination field/variable in said program? The way the data was presented, it could be an ad hoc query run against the db and he wants to copy/paste it into another app.

Guess we'll have to see, but it the O/P made it seem like STRIP may do the job. Does DB2 also have the TRIM(Col1) syntax?