Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

how to truncate unused spaces in a column during select

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 15, 2008 12:26 pm    Post subject: how to truncate unused spaces in a column during select
Reply with quote

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,
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Jul 15, 2008 12:29 pm    Post subject:
Reply with quote

Roopa,

Try STRIPing the column.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 15, 2008 12:43 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 15, 2008 2:40 pm    Post subject:
Reply with quote

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

Thanks,
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Tue Jul 15, 2008 2:46 pm    Post subject: Reply to: how to truncate unused spaces in a column during s
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 15, 2008 8:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Jul 16, 2008 9:45 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us