Oracle to_number函数参数
我在使用 TO_NUMBER 函数的第二个和第三个参数时遇到问题。其中一项是否依赖于另一项？ nls_params 参数如何工作？我不明白查询结果怎么
SELECT TO_NUMBER('17.000,23',
'999G999D99',
'nls_numeric_characters='',.'' ')
REFORMATTED_NUMBER
FROM DUAL;
会是17000.23。有人可以解释一下上述转换的过程吗？
PS 上面的查询摘自一本Oracle数据库SQL专家证书准备书。
I'm having trouble with TO_NUMBER function second and third parameters. Does one of them depend on the other one? How does nls_params parameter work? I can't understand how the the result of the query
SELECT TO_NUMBER('17.000,23',
'999G999D99',
'nls_numeric_characters='',.'' ')
REFORMATTED_NUMBER
FROM DUAL;
can be 17000.23. Could somebody please explain the process of the above conversion.
P.S. The above query is taken from an Oracle Database SQL Expert Certificate preparation book.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您告诉
TO_NUMBER函数，nls_numeric_characters 中的两个字符
,.代表小数点和千位分隔符，因此它将数字视为一万七千点二十三分。
请参阅：http://download.oracle.com/docs/cd/B13789_01/olap.101/b10339/x_stddev022.htm#i78653
you are telling the
TO_NUMBERfunction that,the two characters
,.in nls_numeric_characters represent the decimal and thousand seperatorso it sees the number as seventeen thousand point twenty three.
see: http://download.oracle.com/docs/cd/B13789_01/olap.101/b10339/x_stddev022.htm#i78653
现在，我将回答我自己的问题。在使用
TO_NUMBER函数时，我错过了重要的一点：无论我从TO_NUMBER函数得到什么，都将是一个数字。数字除了小数点和 E 科学计数法外不包含任何其他内容。因此 17,788.99 实际上并不是一个数字，而是 17788.99 的字符串表示形式。如果我们尝试从 17,788.99 中减去 500，我们就会失败。（嗯，Oracle 隐式地将数字字符串转换为数字，反之亦然，但原则上我们不能在字符串和数字之间执行算术运算）。我确信
TO_NUMBER函数几乎从未用于选择列值。它更适合进行算术运算。相反，我们使用 TO_CHAR 以简洁、易于阅读的格式显示列值或任何数字表达式。格式模型和 nls_params 不仅适用于TO_NUMBER函数，也适用于TO_CHAR。Now, I'll answer my own question. While using
TO_NUMBERfunction I missed the important point that, whatever I get fromTO_NUMBERfunction is going to be a number. And a number does not include anything else than decimal point and E scientific notation. So 17,788.99 is not actually a number but is rather the string representation of 17788.99.If we try to subtract 500 from 17,788.99 we'll fail.(Well, Oracle implicitly converts numeric strings to numbers and vice-versa, but principally we can't perform arithmetic operations between strings and numbers). I'm sure that
TO_NUMBERfunction is almost never used to select a column value. It's rather used to be able to make arithmetic operations. Instead, we useTO_CHARto show a column value or any numeric expression in a neat, easy to read format. The fomat models and nls_params are not only forTO_NUMBERfunction, but forTO_CHARas well.