Avatar of diteps06
diteps06
Flag 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?
Oracle Database

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
Ryan Chong

try something like this:

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

Open in new window

Norie

Try this.

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

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Heiko Bialozyt

i was i little to late ... norie is ok
Norie

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
johnsone

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

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.