| Format a String to 2 decimal place [message #618721] |
Tue, 15 July 2014 01:57  |
 |
jimjohn
Messages: 12
Registered: July 2014
|
Junior Member |
|
|
Hi All,
I have 15 digit number stored as varchar2.
I want to decimal format this number to 2 digits.
How can i achieve this.
Ex:
input out put
000000000004166 41.66
000000000009475 94.75
120000000009466 1200000000094.66
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Format a String to 2 decimal place [message #618783 is a reply to message #618762] |
Tue, 15 July 2014 08:59  |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
jimjohn wrote on Tue, 15 July 2014 06:02Here is my solution.
to_char( to_number(AMOUNT/100),'FM9999999.90')
Look at to_number(AMOUNT/100). Amount is a string. So AMOUNT/100 implicitly converts AMOUNT to number and then divides it by 100. Then TO_NUMBER which expects a string implicitly converts number AMOUNT/100 to string and then back to number. Do you see how many unnecessary conversions code performs? Compare it with:
to_char(AMOUNT / 100,'FM9999999.90')
But you could use REGEXP_REPLACE which could be faster than converting string to number then dividing by 100 and then converting it back to string:
regexp_replace(amount,'^0*(.+)(..)$','\1.\2')
and if amount can be negative:
regexp_replace(amount,'^(-)?0*(.+)(..)$','\1\2.\3')
SY.
[Updated on: Tue, 15 July 2014 09:01] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
