如何使用非默认 NLS_NUMERIC_CHARACTERS 在 Oracle PL/SQL 中有效地将文本转换为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4142891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?
提问by bart
I'm trying to find an efficient, generic way to convert from string to a number in PL/SQL, where the local setting for NLS_NUMERIC_CHARACTERS settings is inpredictable -- and preferable I won't touch it. The input format is the programming standard "123.456789", but with an unknown number of digits on each side of the decimal point.
我试图在 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...
to_numberaccepts a 3rd parameter but in that case you to specify a second parameter too, and there is no format spec for "default"...
to_number接受第三个参数，但在这种情况下，您也需要指定第二个参数，并且“默认”没有格式规范......
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...
There is another way using PL/SQL:
还有另一种使用 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;
which does exactlywhat I want, but it doesn't seem efficient if you do it many, many times in a query. You cannot cache the value of v_decimal (fetch once and store in a package variable) because it doesn't know if you change your session value for NLS_NUMERIC_CHARACTERS, and then it would break, again.
这正是我想要的，但如果您在查询中执行很多次，它似乎效率不高。您无法缓存 v_decimal 的值（获取一次并存储在包变量中），因为它不知道您是否更改了 NLS_NUMERIC_CHARACTERS 的会话值，然后它会再次中断。
Am I overlooking something? Or am I worrying too much, and Oracle does this a lot more efficient then I'd give it credit for?
我是否忽略了什么？还是我太担心了，而 Oracle 这样做的效率比我认为的要高得多？
回答by Vincent Malgrat
The following should work:
以下应该工作：
SELECT to_number(:x,
translate(:x, '012345678-+', '999999999SS'),
'nls_numeric_characters=''.,''')
FROM dual;
It will build the correct second argument 999.999999with the efficient translateso you don't have to know how many digits there are beforehand. It will work with all supported Oracle number format (up to 62 significant digits apparently in 10.2.0.3).
它将999.999999以高效的方式构建正确的第二个参数，translate因此您不必事先知道有多少位数字。它将适用于所有支持的 Oracle 数字格式（在 10.2.0.3 中显然最多 62 位有效数字）。
Interestingly, if you have a really big string the simple to_number(:x)will work whereas this method will fail.
有趣的是，如果你有一个非常大的字符串，这个简单的to_number(:x)方法会起作用，而这个方法会失败。
Edit: support for negative numbers thanks to sOliver.
编辑：由于sOliver支持负数。
回答by S?ren Boisen
If you are doing a lot of work per session, an option may be to use ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' at the beginning of your task.
如果您在每个会话中执行大量工作，则可以选择在任务开始时使用 ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'。
Of course, if lots of other code is executed in the same session, you may get funky results :-) However we are able to use this method in our data load procedures, since we have dedicated programs with their own connection pools for loading the data.
当然，如果在同一个会话中执行了很多其他代码，你可能会得到奇怪的结果:-) 但是我们可以在我们的数据加载过程中使用这种方法，因为我们有专门的程序，它们有自己的连接池来加载数据数据。
回答by hvb
Sorry, I noticed later that your question was for the other way round. Nevertheless it's noteworthy that for the opposite direction there is an easy solution:
对不起，我后来注意到你的问题是相反的。不过值得注意的是，对于相反的方向，有一个简单的解决方案：
A bit late, but today I noticed the special format masks 'TM9' and 'TME' which are described as "the text minimum number format model returns (in decimal output) the smallest number of characters possible." on https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210.
有点晚了，但今天我注意到特殊格式掩码“TM9”和“TME”，它们被描述为“文本最小数字格式模型返回（以十进制输出）尽可能少的字符数”。在https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210 上。
It seems as if TM9 was invented just to solve this particular problem:
似乎 TM9 的发明就是为了解决这个特殊问题：
select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;
The result is '1234.5678'with no leading or trailing blanks, and a decimal POINT despite my environ containing NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252, which would normally cause a decimal COMMA.
结果是'1234.5678'没有前导或尾随空格，尽管我的环境包含NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252，但通常会导致十进制逗号。
回答by difr
select to_number(replace(:X,'.',to_char(0,'fmd'))) from dual;
btw
顺便提一句
select to_number(replace('1.2345e-6','.',to_char(0,'fmd'))) from dual;
and if you want more strict
如果你想要更严格
select to_number(translate(:X,to_char(0,'fmd')||'.','.'||to_char(0,'fmd'))) from dual;
