• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

oracle replace function

I have a query

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


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.
  • 2
  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
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.
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
anumosesAuthor Commented:
total_hours is number with a format 9999.99. On saving in the database has to be saved as 1000, also a number.
anumosesAuthor Commented:
this solution worked. Thanks
by the way,  the problem was using replace "before" converting to a string with a format


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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now