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.
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
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.
0
 
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.
0
 
anumosesAuthor Commented:
this solution worked. Thanks
0
 
sdstuberCommented:
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
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.

All Courses

From novice to tech pro — start learning today.