All Forums Database
Alex181 11 posts Joined 08/14
29 Aug 2014
Format with leading zeros

Hi, I need to format the column accnt_id to get the output as result_accnt_id. Any help will be greatly appreciated. Thanks.
 
accnt_id     Result_accnt_id
1               A0000001
2               A0000002
39,000       A0039000
100,962     A0100962
 

Raja_KT 1246 posts Joined 07/09
29 Aug 2014

With your data mentioned, I did this way. There are other ways too.
select cast(regexp_replace(Result_accnt_id,'A','',1,0,'i') as integer) from your_table;
This will work if Result_accnt_id is having the same pattern. If Result_accnt_id is having data like AAAA , then it won't work.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Alex181 11 posts Joined 08/14
29 Aug 2014

Hi Raja,
Thank you very much for your response. my table only has accnt_id and I need the output as result_accnt_id.
input data= 100,962
output= A0100962
Is there a way to do this?

Raja_KT 1246 posts Joined 07/09
29 Aug 2014

oops.. I did the opposite:
you can do something like this
select acct_id,char_length(cast(acct_id as varchar(10))) a,
case when a=6 then 'A0'||cast(acct_id as varchar(10)) END FROM yourtable;
For the , you have you can replace it with regexp mentioned above
similarly for others if you have
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raja_KT 1246 posts Joined 07/09
29 Aug 2014

In case of varchar field, you can do thus:
select regexp_replace(acct_id ,',','',1,0,'i') b,char_length(regexp_replace(acct_id ,',','',1,0,'i') ) a,
case when a=6 then 'A0'||b end from your_table;
Hope I am not confusing you.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Alex181 11 posts Joined 08/14
29 Aug 2014

This worked! thank you so much Raja.

dnoeth 4628 posts Joined 11/04
29 Aug 2014

Simply padding with leading blanks can be done with a FORMAT:

'A' || CAST(acct_id AS FORMAT '9(6)')

 

Dieter

You must sign in to leave a comment.