如何使用非默认 NLS_NUMERIC_CHARACTERS 在 Oracle PL/SQL 中有效地将文本转换为数字？

Question

我试图找到一种高效、通用的方法来在 PL/SQL 中从字符串转换为数字，其中 NLS_NUMERIC_CHARACTERS 设置的本地设置是不可预测的——最好我不会碰它。输入格式为编程标准“123.456789”，但小数点两侧的位数未知。

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

”没有格式规范...

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it's rather pointless...

还有另一种使用 PL/SQL 的方法：

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

to_number 接受第三个参数，但在这种情况下，您也需要指定第二个参数，并且“默认 >正是我想要的，但如果你在查询中执行很多次，它似乎效率不高。您无法缓存 v_decimal 的值（获取一次并存储在包变量中），因为它不知道您是否更改 NLS_NUMERIC_CHARACTERS 的会话值，然后它会再次中断。

我是否忽略了什么？还是我担心太多了，Oracle 的效率比我想象的要高得多？

Answer 1

如果您每次会话要做很多工作，一个选择可能是使用
更改会话集 NLS_NUMERIC_CHARACTERS = '.,'
在你的任务开始时。

当然，如果在同一个会话中执行许多其他代码，您可能会得到奇怪的结果:-)
但是，我们可以在数据加载过程中使用此方法，因为我们有专用程序及其自己的连接池来加载数据。

Answer 2

抱歉，我后来注意到你的问题是反过来的。尽管如此，值得注意的是，对于相反的方向，有一个简单的解决方案：

有点晚了，但今天我注意到特殊格式掩码“TM9”和“TME”，它们被描述为“文本最小数字格式模型返回（在十进制输出中）可能的最小字符数。”在 https://docs.oracle.com/cloud/latest /db112/SQLRF/sql_elements004.htm#SQLRF00210。

TM9 的发明似乎只是为了解决这个特定问题：

select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

结果是 '1234.5678' ，没有前导或尾随空格，并且有一个小数点，尽管我的环境包含 NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252，这通常会导致十进制逗号。

Answer 3

select to_number(replace(:X,'.',to_char(0,'fmd'))) from dual;

顺便说一句

select to_number(replace('1.2345e-6','.',to_char(0,'fmd'))) from dual;

，如果你想要更严格

select to_number(translate(:X,to_char(0,'fmd')||'.','.'||to_char(0,'fmd'))) from dual;

Answer 4

数字数量不受限制是否现实？
如果我们假设是这样，那么这不是更仔细地研究需求的好理由吗？

如果我们遇到初始字符串超长的情况，那么下面的方法就可以解决问题：

select 
  to_number(
    '11111111.2222'
  , 'FM' || lpad('9', 32, '9') || 'D' || lpad('9', 30, '9')
  , 'NLS_NUMERIC_CHARACTERS=''.,'''
  ) 
from 
  dual

Answer 5

以下内容应该有效：

SELECT to_number(:x, 
                 translate(:x, '012345678-+', '999999999SS'), 
                 'nls_numeric_characters=''.,''') 
  FROM dual;

它将使用高效的 translate 构建正确的第二个参数 999.999999，因此您不必事先知道有多少位数字。它将适用于所有受支持的 Oracle 数字格式（在 10.2.0.3 中显然最多为 62 位有效数字）。

有趣的是，如果你有一个非常大的字符串，简单的 to_number(:x) 将会起作用，而这个方法将会失败。

编辑：由于 sOliver 支持负数。