Hi, I need to format the column accnt_id to get the output as result_accnt_id. Any help will be greatly appreciated. Thanks.
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.
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
Is there a way to do this?
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
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.