Oracle：将字符串转换为 int
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11128399/
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: Converting string to int
提问by WOPR
In Oracle, I have a table with a column (X) which can have strings like this:
在 Oracle 中，我有一个带有列 (X) 的表，它可以包含如下字符串：
97M
481
101X
88
21E
etc.
97M
481
101X
88
21E
等
I want to select just those rows where the integral value of x > 90. In this example, I would expect to get back the rows containing values 97M, 101X and 481. How can I do this?
我只想选择 x 的整数值 > 90 的那些行。在这个例子中，我希望取回包含值 97M、101X 和 481 的行。我该怎么做？
回答by Ollie
I used REGEXP_REPLACEto remove the alpha characters before using TO_NUMBERso I could filter the results as needed:
我使用REGEXP_REPLACE在使用之前删除字母字符，TO_NUMBER以便我可以根据需要过滤结果：
WITH t
AS (SELECT '97F' AS x FROM DUAL
UNION
SELECT '481' FROM dual
UNION
SELECT '101A' FROM dual
UNION
SELECT '101A' FROM dual
UNION
SELECT '88' FROM dual
UNION
SELECT '21E' FROM dual)
SELECT x
FROM t
WHERE TO_NUMBER(regexp_replace(x, '[[:alpha:]]', '')) > 90;
X
101A
481
97F
Hope it helps...
希望能帮助到你...
回答by Mike Park
You can always use translate to remove alpha characters.
您始终可以使用 translate 来删除字母字符。
TO_NUMBER(translate('90F', '1ABCDEFGHIJKLMNOPQRSTUFWXYZ', '1')) -- = 90
Translate does a 1 to 1 translation of the characters in the second argument to the characters in the third argument.
Translate 将第二个参数中的字符与第三个参数中的字符进行 1 对 1 的翻译。
Here's a different example.
这是一个不同的例子。
translate('ABCDEFG', 'ABC', 'XYZ') = 'XYZDEFG'
A -> X
B -> Y
C -> Z
Now if you look at my example
现在如果你看看我的例子
translate('90F', '1ABCDEFGHIJKLMNOPQRSTUFWXYZ', '1')
1 -> 1 (this is here because if that last argument is null, you'll get an empty string)
A -> ? there's nothing here, so oracle will translate it to nothing
B -> same as above
回答by Gordon Linoff
You might try the following:
您可以尝试以下操作：
WHERE (substr(x, 1, 1) = '9' and substr(x, 2, 1) between '1' and '9'
) or
(substr(x, 1, 1) between '1' and '9' and
substr(x, 2, 1) between '0' and '9' and
substr(x, 3, 1) between '0' and '9'
)
This is a bit brute force. It checks to see if the string starts with 91-99 or if the first three digits are a string.
这有点蛮力。它检查字符串是否以 91-99 开头或前三位数字是否为字符串。
