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?
LVL 1
diteps06Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
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
Heiko BialozytLeiter ITCommented:
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
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Heiko BialozytLeiter ITCommented:
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
NorieAnalyst 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.