|
View previous topic :: View next topic
|
| Author |
Message |
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi srj1957,
Try like below,
| Code: |
SELECT STRIP('000001107028', L,'0') FROM SYSIBM.SYSDUMMY1;
|
Thanks,
Sushanth |
|
| Back to top |
|
|
Siva SS
New User
Joined: 08 Jul 2013
Posts: 1
Location: India
|
|
|
|
You might also want to try:
| Code: |
| SELECT TRIM(LEADING '0' FROM '000001107028') FROM SYSIBM.SYSDUMMY1; |
|
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
Thanks for your replies....
NOTE : I mis-stated in my original question 
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| If these are account numbers - are they table keys? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Try to RIGHT justify when select and SUBSTR to pick last 10 charecters |
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
Nic - No they are not keys
P-B ..I want to lose all leading zeros |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 |
|
|
|
|
