Oracle如何在同一列中同时处理科学计数法与千位分隔符？

针对你遇到的带千位分隔符的文本转数字报错问题，有几种实用的解决方式：

方法1：移除千位分隔符后转换 #

直接用正则表达式替换掉文本中的逗号，再调用TO_NUMBER转换，这种方式能兼容科学计数法、普通小数和带千位分隔符的数字：

SELECT val, 
       TRUNC(TO_NUMBER(REGEXP_REPLACE(val, ',', '')), 0), 
       MOD(TO_NUMBER(REGEXP_REPLACE(val, ',', '')), 1)
from (
    select '8E4' val from dual union
    select '8E-4' val from dual union
    select '1,234.567' val from dual union
    select '1.234' val from dual
);

方法2：用多格式掩码匹配转换 #

利用OracleTO_NUMBER函数支持多格式掩码的特性，同时指定千位分隔符格式和科学计数法格式，还可以显式指定数字分隔符规则避免NLS设置影响：

SELECT val,
       TRUNC(TO_NUMBER(val, '9G999D999,9.9EEEE', 'NLS_NUMERIC_CHARACTERS=''.'''','''), 0),
       MOD(TO_NUMBER(val, '9G999D999,9.9EEEE', 'NLS_NUMERIC_CHARACTERS=''.'''','''), 1)
FROM (
    select '8E4' val from dual union
    select '8E-4' val from dual union
    select '1,234.567' val from dual union
    select '1.234' val from dual
);

方法3：先校验再转换（避免无效值报错） #

如果数据中存在非数字格式的无效值，可以用VALIDATE_CONVERSION先校验，再处理，防止整个查询失败：

SELECT val,
       CASE WHEN VALIDATE_CONVERSION(REGEXP_REPLACE(val, ',', '') AS NUMBER) = 1
            THEN TRUNC(TO_NUMBER(REGEXP_REPLACE(val, ',', '')), 0)
            ELSE NULL -- 可替换为自定义默认值或处理逻辑
       END AS truncated_val,
       CASE WHEN VALIDATE_CONVERSION(REGEXP_REPLACE(val, ',', '') AS NUMBER) = 1
            THEN MOD(TO_NUMBER(REGEXP_REPLACE(val, ',', '')), 1)
            ELSE NULL
       END AS decimal_part
from (
    select '8E4' val from dual union
    select '8E-4' val from dual union
    select '1,234.567' val from dual union
    select '1.234' val from dual union
    select 'invalid_text' val from dual
);

注意：你原SQL中的MOD(val, 1) - 1会得到负数，推测可能是笔误，MOD(数字, 1)本身就能获取小数部分，所以上面的示例中去掉了-1，如果有特殊需求可以自行调整。

内容的提问来源于stack exchange，提问作者gt.guybrush