Using TO_NUMBER to parse numbers both in fixed notation and scientific notation

Question

Using Oracle SQL, how can I convert string to a number when the string (a VARCHAR2 column) can contain either:

• a number in fixed notation, such as -11.496606820938826

  SELECT TO_NUMBER('-11.496606820938826', '999.999999999999999999', 'NLS_NUMERIC_CHARACTERS=''. ''') FROM DUAL;
  

• a number in scientific notation, such as -4.0504035245989348E-3

  SELECT TO_NUMBER('-4.0504035245989348E-3', '999.999999999999999999EEEE', 'NLS_NUMERIC_CHARACTERS=''. ''') FROM DUAL;
  

So far, I have used the following format models:

• Fixed notation: 999.999999999999999999
• Scientific notation: 999.999999999999999999EEEE

but I can't find a single format model which works is both cases.

Example data:

 STRING_VALUE
------------------------
 -4.0504035245989348E-3 
 -11.496606820938826    
------------------------

I am using Oracle 12.1, so I can't use either VALIDATE_CONVERSION nor ON CONVERSION ERROR.

Edit

Moreover, this code is going to run on different enviroments with different NLS settings; I can't assume that using TO_NUMBER without a format model would just work.

Answer 1

How about using a CASE statement?

CREATE TABLE t (v VARCHAR2(30));
INSERT INTO t VALUES ('-11.49660682093882');
INSERT INTO t VALUES ('-4.0504035245989348E-3');

SELECT v, CASE WHEN v LIKE '%E%' 
           THEN TO_NUMBER(v, '999.999999999999999999EEEE', 'NLS_NUMERIC_CHARACTERS=''. ''')
           ELSE TO_NUMBER(v, '999.999999999999999999',     'NLS_NUMERIC_CHARACTERS=''. ''') 
          END AS x
  FROM t;

V                        X
-11.49660682093882       -11,49660682093882
-4.0504035245989348E-3   -0,0040504035245989348