Link to home
Start Free TrialLog in
Avatar of diteps06
diteps06Flag for United States of America

asked on

Insert decimal places based on another column in Oracle.

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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try something like this:

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

Open in new window

Avatar of Norie
Norie

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
i was i little to late ... norie is ok
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
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

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
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

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.