如何使用带有固定小数点字符的 TO_NUMBER 函数在 Oracle 中将字符串转换为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5552359/
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 convert from string to number in Oracle using TO_NUMBER function with fixed decimal point char?
提问by Tom
I need to convert in procedure from stringto decimalwith fixed decimal separator .independant on culture settings. Next, I know decimal number is limited just with 6 decimal places after .and there is no limitiation on number of digits before .. Using Oracle documentation and its examples for format strings I have now just this solution:
我需要使用独立于文化设置的固定小数分隔符在程序中转换string为。接下来，我知道十进制数只限制在 6 个小数位之后，并且前面的位数没有限制。使用 Oracle 文档及其格式字符串示例我现在只有这个解决方案：decimal...
v_number := TO_NUMBER(v_string, '9999999999999999999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''. ''');
Number of 9chars before Dis maximum number allowed. I find this format string as pretty awful. Is there any better format string for this general conversion or some way to omit second parameter of function? In general I just need to pass to function NLS parameter to tell it i just want to convert with decimal separator ., but second parameter is mandatory in that case as well.
9前面的字符D数是允许的最大数量。我发现这个格式字符串非常糟糕。对于这种通用转换，是否有更好的格式字符串或某种方法可以省略函数的第二个参数？一般来说，我只需要传递给函数 NLS 参数来告诉它我只想用小数点分隔符进行转换.，但在这种情况下第二个参数也是必需的。
回答by Vincent Malgrat
You can't call the to_numberfunction with the third parameter and not the second. I would suggest putting the "ugly" format string in a package constant and forget about it.
您不能to_number使用第三个参数而不是第二个参数调用该函数。我建议将“丑陋”格式字符串放在包常量中并忘记它。
You could also use dbms_session.set_nlsto modify your NLS settings and be able to use to_numberwithout arguments.
您还可以dbms_session.set_nls用于修改您的 NLS 设置，并且to_number无需参数即可使用。
回答by Jostein Topland
Handles both comma and period.
处理逗号和句点。
FUNCTION to_number2(p_num_str VARCHAR2) RETURN NUMBER AS
BEGIN
RETURN TO_NUMBER(REPLACE(p_num_str, ',', '.'), '999999999999D999999999999', 'NLS_NUMERIC_CHARACTERS=''.,''');
END;
回答by DENIS
CREATE OR REPLACE FUNCTION IS_NUMBER(P_VAR IN VARCHAR2)
RETURN NUMBER
IS
P_NUMBER NUMBER := 0;
RIG VARCHAR2(10) := '';
FORMAT VARCHAR2(100) := '999999999999D999999999999';
RES VARCHAR2(100) := '';
BEGIN
SELECT VALUE INTO RIG
FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
IF SUBSTR(RIG,1,1) = '.' THEN
RES := REPLACE(P_VAR,',','.');
ELSE
RES := REPLACE(P_VAR,'.',',');
END IF;
P_NUMBER := TO_NUMBER(RES,FORMAT,'NLS_NUMERIC_CHARACTERS='''||RIG||'''');
P_NUMBER := ROUND(P_NUMBER,5); --FIVE DIGITS AFTER DECIMAL POINT IS ENOUGH
RETURN P_NUMBER;
EXCEPTION
WHEN OTHERS THEN RETURN -1;
END;
回答by Ravindra Vemula...
select to_number(' 12.5 ') + to_number(' 12 ') from dual;
