Format with leading zeros

Database
Enthusiast

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

6 REPLIES
Enthusiast

Re: Format with leading zeros

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.

Enthusiast

Re: Format with leading zeros

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?

Enthusiast

Re: Format with leading zeros

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

Enthusiast

Re: Format with leading zeros

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.

Enthusiast

Re: Format with leading zeros

This worked! thank you so much Raja.

Senior Apprentice

Re: Format with leading zeros

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

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