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
 

 

Replacing charactrers

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

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jul 12, 2013 1:24 am    Post subject: Replacing charactrers
Reply with quote

I have a column that can contain 4 different types of Account number.
e.g.
ACCT_NUM
000001107028
1107028
A0001107028
M1107028

I want to remove leading zeros
    remove leading zeros
    Remove the 'A'
    Remove the 'M'

so that I end up with the 4 account numbers numbers looking like this

1107028
1107028
1107028
1107028

I have tried this
Code:

SELECT COL_A,
   REPLACE(
   REPLACE(
   REPLACE(ACCT_NUM
   ,'0','')
   ,'M','')
   ,'A','')AS X
from table

and I get this
11728
11728
11728
11728

I want to remove only LEADING ZEROES not replace all the zeros...

Any suggestions would be greatly appreciated on how to solve this...
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Jul 12, 2013 10:14 am    Post subject:
Reply with quote

Hi srj1957,

Try like below,
Code:
SELECT STRIP('000001107028', L,'0') FROM SYSIBM.SYSDUMMY1;


Thanks,
Sushanth
Back to top
View user's profile Send private message
Siva SS

New User


Joined: 08 Jul 2013
Posts: 1
Location: India

PostPosted: Fri Jul 12, 2013 11:03 am    Post subject:
Reply with quote

You might also want to try:
Code:
 SELECT TRIM(LEADING '0' FROM '000001107028') FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jul 12, 2013 5:03 pm    Post subject: Reply to: Replacing APLHA and ZERO characters
Reply with quote

Thanks for your replies.... I have tried those before, they remove ONLY the zero, the main issue is the ones with the ALPHA characters at the beginning

A0001107028
M1107028

to look like this

1107028
1107028

I having trouble finding a sloution...
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jul 12, 2013 5:16 pm    Post subject:
Reply with quote

Thanks for your replies....

NOTE : I mis-stated in my original question icon_rolleyes.gif
I didn't mean to say that I get this.. I meant to say I WANT this.
1107028
1107028
1107028
1107028


I have tried those before, they remove ONLY the zero, the main issue is the ones with the ALPHA characters at the beginning

A0001107028
M1107028

I want the result to look like this...
1107028
1107028



I having trouble finding a sloution...
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1717
Location: UK

PostPosted: Fri Jul 12, 2013 6:05 pm    Post subject:
Reply with quote

If these are account numbers - are they table keys?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jul 12, 2013 6:15 pm    Post subject:
Reply with quote

Try to RIGHT justify when select and SUBSTR to pick last 10 charecters
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jul 12, 2013 6:44 pm    Post subject: Reply to: Replacing charactrers
Reply with quote

Nic - No they are not keys

P-B ..I want to lose all leading zeros
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jul 12, 2013 8:06 pm    Post subject:
Reply with quote

Hi,

Try below query, I have just combined solutions provided in earlier posts

Code:
SELECT TRIM (LEADING '0' FROM (REPLACE(REPLACE('A0001107028','A','0'),'M','0'))) FROM SYSIBM.SYSDUMMY1;


Reagrds,
Chandan
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jul 12, 2013 9:37 pm    Post subject: Reply to: Replacing charactrers
Reply with quote

Thanks for the suggestions... I came up with this that works


Code:
SELECT COL_A,
CASE SUBSTR(COL_A,1,1)
WHEN 'A' THEN TRIM(L '0' FROM SUBSTR(COL_A,2,19))
WHEN 'M' THEN TRIM(L '0' FROM SUBSTR(COL_A,2,19))
WHEN '0' THEN TRIM(L '0' FROM COL_A)
ELSE COL_A END
FROM   TABLE
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 Replacing Start Transid with LINK pkmurali CICS 12 Tue Apr 19, 2016 8:22 pm
No new posts ADRDSSU Restore - How to prevent repl... Lipi.Prasannan JCL & VSAM 12 Fri Oct 02, 2015 12:13 am
No new posts Matching and replacing JOIN KEYS for ... JAYACHANDRAN THAMPY SYNCSORT 17 Mon Feb 16, 2015 5:03 am
No new posts Replacing GO TO Statements hailashwin COBOL Programming 3 Tue Dec 02, 2014 5:28 pm
No new posts Replacing record Based on matching st... mrdinesh DFSORT/ICETOOL 7 Thu Sep 25, 2014 8:22 pm


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