We help IT Professionals succeed at work.

Insert decimal places based on another column in Oracle.

diteps06
diteps06 asked
on
129 Views
Last Modified: 2019-02-19
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

CERTIFIED EXPERT

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

Commented:
Try this.

SELECT VAL, DCP, VAL/POWER(10, DCP)
FROM MyTable
Heiko BialozytLeiter IT

Commented:
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
Heiko BialozytLeiter IT

Commented:
i was i little to late ... norie is ok
NorieAnalyst Assistant
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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

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.