Just Code‎ > ‎

Oracle 12c - Convert scientific formatted varchar string to Number type

posted Oct 14, 2014, 1:19 AM by Peter Henell   [ updated Dec 22, 2014, 2:36 PM ]
Scientific formatted numbers are convenient and sometimes you need to deal with them anyway, convenient or not. 
Given a perfectly fine number, given as a string, how can we convert that to a Number type?

Note that if the value is presented as a pure number, then Oracle will manage it perfectly:

select 0.0000000000000000001e+00 as a,
       0.0000000000000000001e+00 + 10e20 as b
from dual;
--         A          B
------------ ----------
--,0000000000000000001    1.0E+21
But if you have the value as a string, in a column or as a parameter then Oracle will fail to convert it to a number, unless you instruct Oracle of how to interpret the string.
-- Given this scientific representation of a number, convert it to a Number data type.
-- Straight forward cast will fail
select cast('0.0000000000000000001e+00' as number) from dual;
--Error starting at line : 3 in command -
--select cast('0.0000000000000000001e+00' as number) from dual
--Error report -
--SQL Error: ORA-01722: invalid number
--01722. 00000 -  "invalid number"
--*Cause:    The specified number was invalid.
--*Action:   Specify a valid number.

-- Jump through the hoops to get it
    to_number('0.0000000000000000001e+00', '99999999999999999999.99999999999999999999EEEE', 'NLS_NUMERIC_CHARACTERS = ''.,''') as a
from dual;