Solved

oracle replace function

Posted on 2011-09-09
5
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

623 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