Insert decimal places based on another column in Oracle.

diteps06
diteps06 used Ask the Experts™
on
I have 2 cols (VAL, DCP) which are a number and its required decimal places.
I will like to change the number to it's required decimal places define in another column.

For example
VAL           DCP
5600      2
6540      1
65896      3

I will like a third column NUM_DCP as follows:
VAL                DCP      NUM_DCP
5600          2              56.00
6540         1             65.40
65896         3            65.896

I
How can I achieve it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
try something like this:

Select *, 
concat(
  substr(VAL, 1, length(VAL)-DCP),
  '.',
  substr(VAL, -1*DCP)
) NUM_DCP
from yourTable

Open in new window

NorieAnalyst Assistant

Commented:
Try this.

SELECT VAL, DCP, VAL/POWER(10, DCP)
FROM MyTable
i expect that val is numeric and dcp also. so you can compute it by

select val / power(10, dcp) as new_val from ...

it's fast and easy

Good luck
Heiko
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

i was i little to late ... norie is ok
NorieAnalyst Assistant

Commented:
This should also set the required no of decimal places.

SELECT VAL, DCP, TO_CHAR(VAL/POWER(10, DCP), '9999.'||RPAD('9', DCP, '9'))
FROM MyTable
Analyst Assistant
Commented:
Oops, forgot the column alias.

SELECT VAL, DCP, TO_CHAR(VAL/POWER(10, DCP),  RPAD('9', LENGTH(VAL)-DCP, '9')||'.'||RPAD('9', DCP, '9')) AS NUM_DCP
FROM MyTable
johnsoneSenior Oracle DBA

Commented:
Why all the suggestions of character manipulation?  This is from the original post:
I will like to change the number
That tells me it is a number.

Also, this:
Select *, 
concat(
  substr(VAL, 1, length(VAL)-DCP),
  '.',
  substr(VAL, -1*DCP)
) NUM_DCP
from yourTable

Open in new window

isn't anywhere near being able to run.  To make that run, it would have to look more like:
SELECT yourtable.*, 
       Concat(Concat(Substr(val, 1, Length(val) - dcp), '.'), 
       Substr(val, -1 * dcp)) 
       NUM_DCP 
FROM   yourtable; 

Open in new window

NorieAnalyst Assistant

Commented:
The only reason I added the TO_CHAR was to match  (almost - see the 2nd row) the requested output.

If they do want a numeric value then I suppose they could use what I originally posted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial