oracle varchar 到数字的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8178727/
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
Conversion of varchar to number
提问by JLearner
I have a question that bothers me. How can i convert a varchar to number when inside the varchar value consists of alphabets.
我有一个困扰我的问题。当 varchar 值由字母组成时，如何将 varchar 转换为数字。
For my varchar price column values:
对于我的 varchar 价格列值：
14dollars10cents
15dollars20cents
By converting it to varchar to number price column, the values should be:
通过将其转换为 varchar 到数字价格列，值应为：
1410
1520
I know that if the varchar does not consists any alphabets, it can auto convert by"
我知道如果 varchar 不包含任何字母，它可以通过“自动转换”
SELECT CONVERT(INT, PRICE) FROM Table
Is there any way to get rid of the alphabets in the middle as I would like to do mathematical function on it.
有什么办法可以去掉中间的字母，因为我想对它做数学函数。
Updated attempt of putting fixed point number in:
更新将定点数放入的尝试：
SELECT CAST (Replace(REPLACE(PRICE, 'dollars', '.'),'cents','') AS Number(4,2)))
FROM TEST;
Thanks
谢谢
采纳答案by adam2510
SELECT CAST(REPLACE(YourVarcharCol, 'dollars', '') AS INT) FROM Table
The issue with this is it will break if the varchar still contains alpha-numeric characters.
问题是如果 varchar 仍然包含字母数字字符，它会中断。
回答by Ollie
You could just use REGEXP_REPLACEto remove all non digit characters:
您可以使用REGEXP_REPLACE删除所有非数字字符：
SELECT REGEXP_REPLACE(price, '[^[:digit:]]')
FROM table;
To then convert this to a number:
然后将其转换为数字：
SELECT TO_NUMBER(REGEXP_REPLACE(price, '[^[:digit:]]'))
FROM table;
If you want to add the point in then you can do that with REGEXP_REPLACEtoo:
如果你想添加点，那么你也可以这样做REGEXP_REPLACE：
SELECT TO_NUMBER(REGEXP_REPLACE(val, '^([0-9]*)([^[:digit:]]*)([0-9]*)(.*)$', '.'))
FROM table;
Voila...
瞧...
回答by Kevin Burton
How about using translateto strip out the unwanted characters.
如何使用translate去除不需要的字符。
SELECT TO_NUMBER(TRANSLATE('14dollars10cents','1234567890dolarscents','1234567890')) FROM DUAL
回答by Sandip
No I don't think there is direct way. you can do string parsing to get your integer value.
不，我认为没有直接的方法。您可以进行字符串解析以获取您的整数值。
