Solved

oracle replace function

Posted on 2011-09-09
5
432 Views
Last Modified: 2012-05-12
I have a query

  select  to_number(replace(10.00,'.'),'9999D00') as total_hours from dual

TOTAL_HOURS
10

--------------
I need to format this to 1000 tthat is what is being stored in the database. I tired 9999, 9900, 9999D00 but am not aboe to format. Help is appreciated.
0
Comment
Question by:anumoses
  • 2
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36510046
Try this:
select  to_number(replace('10.00','.'),'0000D00') as total_hours from dual;


I added single quotes to the 10.0 to remove implicit conversion issues.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 125 total points
ID: 36510057
replace only works on strings   10.00 is a number

so you'll get an implicit conversion

what are you really trying to do?


why not just do...  

( 10.00 * 100 )

and not try to do all the string manipulations
0
 
LVL 6

Author Comment

by:anumoses
ID: 36510090
total_hours is number with a format 9999.99. On saving in the database has to be saved as 1000, also a number.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36510118
this solution worked. Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36510189
by the way,  the problem was using replace "before" converting to a string with a format


to_number(replace(to_char(10.00,'fm99999D00'),'.'))

I still don't recommend this though,  simply multiplying by 100 is not only easier but more efficient

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question