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?
Ryan Chong

try something like this:

``````Select *,
concat(
substr(VAL, 1, length(VAL)-DCP),
'.',
substr(VAL, -1*DCP)
) NUM_DCP
from yourTable``````
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
Norie

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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``````
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; ``````
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.