Number formatting in sql

Question

I have field value in oracle that needs to be manipulated as below for output

value*100

Example:

• if it is 172.24 to be shown as 17224.0
• if it is 13.4567 to be shown as 1345.67

Please suggest how this should be handled in Oracle. I tried implementing it using case but that didn't work out.

Answer 1

Try:

select to_char(value*100,'FM9999990.09') from data;

For example (using the with clause just to set up some test data):

with data as
 (select 13.4567 value from dual
 union all
 select 13.456 value from dual
 union all
 select 13.45 value from dual
 )
select to_char(value*100,'FM9999990.09') from data;

1345.67
1340.6
1300.0

Answer 2

You could do this-

SELECT (value*100) "New value" 
FROM yourTable;