How to remove extra spaces from a column

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
sandeepforever007
Member
Posts: 10
Joined: Wed Apr 02, 2014 9:59 pm

How to remove extra spaces from a column

Post by sandeepforever007 » Thu Jun 12, 2014 3:43 pm

Hi Team,

We are using unload utility to unload the data from the tables and sort the extracted data as per the req


Found that there is null issue accuring for the 2 columns mentioned

Pls find the code used for the same

1. Th code mentioned below is used to extract first name from the name and save it as varchar(15)

Code: Select all

 CAST(RTRIM(SUBSTR(ColumnName,1,LOCATE(' ',ColumnName)-1)) 
 AS VARCHAR(15)),    
2. This column is to display decimal column and varchar col and replace the decimal point with space .

Code: Select all

(REPLACE(CAST(TRIM(ColumnName) 
AS VARCHAR(14) ),'.','')||'"'        

Found that the query is giving null space for eg as shown below

Code: Select all

PRADEEPnulnulnulnulnulnulnulnul
for this column the size is 15 and after name all the spaces are shown as above

The same is the case with the other column

The target system is not accepting the file because of this issue.
Could you please help us resolve this issue

Regards,
Pradeep

watsonsaho
Member
Posts: 2
Joined: Fri Feb 13, 2015 9:44 am
Contact:

Post by watsonsaho » Fri Feb 13, 2015 10:17 am

Your making me think. What to do about it, you have to say on the topic.

Chesterlu
Member
Posts: 2
Joined: Thu Sep 03, 2015 1:25 pm
Contact:

Post by Chesterlu » Thu Sep 03, 2015 1:35 pm

The story turns out to be the same. But it was so cool.[/code]

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others