Oracle SQL, PLSQL, APEX How To's by Dr. S. Raghunathan - HTML preview

CHR-2-DATE

- - - - - - - - - -

26-AUG-61

14.04      How to convert formatted char type data as numeric type

3,  To-number( ) Transforms a char or varchar2 into a number, For example if we want to add the two formatted numbers 1,961,26 + 1,024,65, our system says it is an invalid numbers, In otherwise formatted numbers losses  it  numeric type data status and converted in terms of char 1 varchar2 data type, Hence we need to convert this char data type to numeric form and then we have to add      using      the      syntax       :       "select to-number('1,961,26','99,99,999,99')                  + to-number('1,024,65','99,99,999,99')    as    total from dual" will produce correct result as

TOTAL

- - - - - -

2985,91