Numeric field in desired format.

[didajosh]

Hi,
I am trying to extract data for payroll, from various tables.
The result that is extracted, has to be sent for automatic transmission to a bank.
Now they want amount in following format:
eg. if amount is 47.50, it should be 0000004750.
In 10 digits.

What do I do..??
Please help.

Regards,
Dipali

[didajosh]

Hi,
I just tried:
TO_CHAR(Amount, '0000000000')

i mean 47.50, it should be 0000004750
but it shows 0000000048

it works..! but it is completely rounding the figures.
I don't want it to round..
any soultions.

[Pilgrim333]

Hi,

Just a simple workaround, multiply the amount by 100 and then use the conversion function.
TO_CHAR(Amount * 100 ,'0000000000')

Pilgrim.

[amitpatel66]

@Dipali,

Can you confirm how the numbers are goning to be in Amount column? If they are going to vary say without any decimal or with decimal place of 3 digits then multiplying by 100 will not work. Can you post few different values of amount columns so that we can provide a generalized solution for any type of number value

[Pilgrim333]

Hi Didajosh,

Depending on the number of decimals that should be in the output file, you can use the following (it's generic, no matter how many decimals the number has)

The amount will be rounded to the number of decimals that are needed in the output file. E.g. 123.456 will be rounded to 000012346 if the number of decimals in the output file is 2.

The code you can use is:

to_char(replace(to_char(amount ,'000000.00'),'.'), '0000000000')

In the above code, i just added 2 decimals, but if you want more decimals, then just add another zero in the first format, after the period.

Hope this helps you.

Pilgrim.

[amitpatel66]

Solution have to be generalised for any number of decimal places.if it is 2 decimal places, then multiply by 100 would also have worked.

Dipali,

Just fetch some amount values and post back here for reference.

The below query that came around my mind now is

[code=oracle]
select salary,to_char( REPLACE(salary, '.',''),'000000 0000') formatted_sal from emp

salary formatted_sal
---------- ---------------------
47.50 0000000475 -- For this there is a zero at second decimal place
47.55 0000004755
47.556 0000047556
[/code]

This will work fine for all the numbers of any format. But in case if the number is 47.50 (ending with 0) then it shows 0000000475. If this is fine for you because a Zero at the second decimal place is of no value, then the above query would work perfectly for you.

[didajosh]

@Pilgrim
Thank you soo much for both your solutions!!! It was a great help.
Since decimal places were up to only two, both solutions were viable to me.

@ Amit
Following are some fields from amount field:
647.36
3285.84
1979.2
0
1781.28
so basically it is up to two decimal places.

I have RECENTLY REALIZED that the last two digits in the 10 digit format belong to cents.
So, 47.50 should be ------ 0000004750 and not 0000000475
b'coz it will be interpreted as $4.75 instead of $47.50.
And thank you very much for your solution.
I appreciated you investing your time for the solution.

Pilgrim and Amit, all the solutions really helped me to get an insight of the concept. Once again thank-you, your help means a lot.

[amitpatel66]

Good to hear that the solutions provided had helped you to meet your requirement. Do post back in case of any further issues.

MODERATOR