sql - 使用 TO_NUMBER 以固定记数法和科学记数法解析数字

Question

使用 Oracle SQL，当字符串(VARCHAR2 列)可以包含以下任一内容时，如何将字符串转换为数字:

固定符号的数字，例如 -11.496606820938826

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

以科学记数法表示的数字，例如 -4.0504035245989348E-3

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

到目前为止，我已经使用了以下 format models :

固定符号:999.999999999999999999

科学记数法:999.999999999999999999EEEE

但我找不到适用于两种情况的单一格式模型。

示例数据:

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

我使用的是 Oracle 12.1，所以我不能使用 VALIDATE_CONVERSION也不是 ON CONVERSION ERROR .

编辑

此外，此代码将在具有不同 NLS 设置的不同环境中运行；我不能假设使用 TO_NUMBER 没有格式模型就可以工作。

Answer 1

使用 CASE 怎么样？陈述？

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