Update field value to decimal
Suppose there is a field salary and the value is 9125462, the rightmost number(2) indicates the number of values after decimal. We have to update the same for all records. Eg for above number 9125.46 should be updated, for 452861, it should be updated as 4528.6. How will you do this?
Re: Update field value to decimal
SQL> SELECT * FROM XXX;
A
---------
9125462
452861
SQL> UPDATE XXX
2 SET A = (TRUNC(A/100)||'.'||MOD(A,100)) ;
2 rows updated.
SQL> SELECT * FROM XXX;
A
---------
91254.62
4528.61
Re: Update field value to decimal
use this in ur updation
select decode(substr(&num,length(&num)),2,substr(&num,1,length(&num)-1)/100,1,substr(&num,1,length(&num)-1)/10,3,substr(&num,1,length(&num)-1)/1000) from dual