oracle 字符到数字转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9074589/
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
Character to number conversion error
提问by Bishan
declare
l_tot number := 0;
begin
for i in 1..apex_application.g_f08.count loop
l_tot := l_tot + nvl(to_number(apex_application.g_f08(i)),0);
end loop;
if l_tot = nvl(to_number(:P21_TOTAL_PRICE),0) then
return true;
else
return false;
end if;
end;
Got below error with above code
上面的代码出现以下错误
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Error occurred with :P21_TOTAL_PRICE. What is the wrong ? How can i correct this ?
发生错误:P21_TOTAL_PRICE。怎么了？我该如何纠正？
采纳答案by Sathyajith Bhat
The error rises because the number that you're representing is actually a character string involving commas etc. When you put a to_number to that, Oracle cannot replace the commas.
错误增加是因为您表示的数字实际上是一个包含逗号等的字符串。当您将 to_number 放入其中时，Oracle 无法替换逗号。
You might want to use replacefunction to strip off the commas
您可能想使用替换功能来去除逗号
Change
改变
if l_tot = nvl(to_number(:P21_TOTAL_PRICE),0) then
to
到
if l_tot = nvl(to_number(replace(:P21_TOTAL_PRICE,',','')),0) then
回答by Ollie
Rather than using REPLACE you should use the more powerful REGEXP_REPLACE function. http://www.orafaq.com/wiki/REGEXP_REPLACE
您应该使用更强大的 REGEXP_REPLACE 函数，而不是使用 REPLACE。http://www.orafaq.com/wiki/REGEXP_REPLACE
You can then remove any non-numeric character from the string before then using the TO_NUMBERfunction.
然后，您可以在使用该TO_NUMBER函数之前从字符串中删除任何非数字字符。
In your case it would be something like:
在你的情况下，它会是这样的：
REGEXP_REPLACE(:P21_TOTAL_PRICE, '[^0-9]+', '');
See my answer to almost the exact same question here: Oracle To_Char function How to handle if it's already a string
在这里查看我对几乎完全相同的问题的回答：Oracle To_Char 函数如何处理，如果它已经是一个字符串
