Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

oracle replace function

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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anumoses

ASKER

total_hours is number with a format 9999.99. On saving in the database has to be saved as 1000, also a number.
this solution worked. Thanks
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