string ORACLE - 字符串到数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13065555/
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
ORACLE - String to number
提问by MrMime
I have a little problem with a column on a table. The column is a Varchar named "prize". The datas are something like:
我对桌子上的一列有一点问题。该列是一个名为“prize”的 Varchar。数据是这样的：
00008599
00004565
00001600
etc...
They have to become:
他们必须成为：
85.99
45.65
16.00
etc...
I have tried with to_number function but it doesnt work. Something like:
我试过 to_number 函数，但它不起作用。就像是：
SELECT to_number(prize, '999999.99') FROM TABLE
The error is: ORA-01722
错误是：ORA-01722
回答by Frank Schmitt
You could use LTRIM to get rid of leading zeroes and divide by 100:
您可以使用 LTRIM 去除前导零并除以 100：
SELECT to_number(ltrim(prize, '0')) / 100 FROM table
回答by DJPeter
Please notice that you yourself have to handle the fact that the string is 100 times to great. Easiest way should be something like this:
请注意，您自己必须处理字符串是 100 倍的事实。最简单的方法应该是这样的：
SELECT to_number(prize)/100 FROM TABLE
