SQL 在 Oracle 上选择字符串作为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9890883/
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
Select string as number on Oracle
提问by frenetix
I found this odd behavior and I'm breaking my brains with this... anyone has any ideas?
我发现了这种奇怪的行为，我正在为此伤脑筋……有人有什么想法吗？
Oracle 10g: I have two different tables, both have this column named "TESTCOL" as Varchar2(10), not nullable.
Oracle 10g：我有两个不同的表，都将这一列命名为“ TESTCOL”作为Varchar2 (10)，不可为空。
If I perform this query on table1, i get the proper results:
如果我在table1上执行这个查询，我会得到正确的结果：
select * from table1 where TESTCOL = 1234;
Notethat I'm specifically not placing '1234'... it's not a typo, that's a dynamic generated query and I will try not to change it (at least not in the near future).
请注意，我并没有特别放置“1234”……这不是打字错误，这是一个动态生成的查询，我将尽量不更改它（至少在不久的将来不会更改）。
But, if I run the same query, on table2, I get this error message:
但是，如果我在table2上运行相同的查询，则会收到以下错误消息：
ORA-01722: Invalid number
Both queries are run on the same session, same database.
两个查询都在同一个会话、同一个数据库上运行。
I've been joining these two tables by that column and the join works ok, the only problem shows whenever I try to use that condition.
我一直通过该列连接这两个表，并且连接工作正常，每当我尝试使用该条件时都会显示唯一的问题。
Any ideas on what could be different from one table to the other?
关于一张桌子与另一张桌子有什么不同的任何想法？
Thanks in advance.
提前致谢。
回答by Lukas Eder
If TESTCOLcontains non-numbers, then Oracle might run into problems when converting TESTCOLentries to numbers. Because, what it does internally, is this:
如果TESTCOL包含非数字，则 Oracle 在将TESTCOL条目转换为数字时可能会遇到问题。因为，它在内部所做的是：
select * from table1 where TO_NUMBER(TESTCOL) = 1234;
If you're so sure that 1234cannot be expressed as a VARCHARliteral, then try this instead, in order to compare varchar values, rather than numeric ones:
如果您确定1234不能表示为VARCHAR文字，请尝试使用此方法，以便比较 varchar 值，而不是数字值：
select * from table1 where TESTCOL = TO_CHAR(1234);
回答by APC
Well obvious TABLE2.TESTCOL contains values which are not numbers. Comparing a string to a numeric literal generates an implicit conversion. So any value in TESTCOL hich cannot be cast to a number will hurl ORA-1722.
很明显 TABLE2.TESTCOL 包含不是数字的值。将字符串与数字文字进行比较会生成隐式转换。因此 TESTCOL 中的任何值都不能转换为数字，将抛出 ORA-1722。
It doesn't hit you where you compare the two tables because you are comparing strings.
它不会影响您比较两个表的位置，因为您正在比较字符串。
So you have a couple of options, neiher of which you will like. The most obvious answer is to clean the data so TABLE2 hdoesn't contain non-numerics. Ideally you should combine this with changing the column to a numeric data type. Otherwise you can alter the generator so it produces code you can run against a shonky data model. In this case that means wrapping literals in quote marks if the mapped column has a character data type.
所以你有几个选择，你不会喜欢的。最明显的答案是清理数据，使 TABLE2 hdoes不包含非数字。理想情况下，您应该将此与将列更改为数字数据类型相结合。否则，您可以更改生成器，使其生成可以针对不完善的数据模型运行的代码。在这种情况下，这意味着如果映射列具有字符数据类型，则将文字用引号括起来。
回答by a_horse_with_no_name
You are hitting the perils of implicit typecasting here.
您在这里遇到了隐式类型转换的危险。
With the expression testcol = 1234you state that you want to treat testcolas a numeric column, so Oracle tries to convert all values in that column to a number.
使用testcol = 1234您声明要testcol视为数字列的表达式，因此 Oracle 尝试将该列中的所有值转换为数字。
The ORA-01722 occurs because apparently at least one value in that column is nota number.
出现 ORA-01722 是因为该列中显然至少有一个值不是数字。
Even though you claim that this is "not a typo" it indeed is one. It's a syntactical error.
尽管您声称这“不是错字”，但它确实是错字。这是一个语法错误。
You will have to declare your parameter as a string literal using single quotes: where testcol = '1234'
您必须使用单引号将参数声明为字符串文字： where testcol = '1234'
Creating a correct condition is the only solution to your problem.
创建正确的条件是解决问题的唯一方法。
回答by Johan Hoogenboezem
The following should work. Just replace the "your where".
以下应该工作。只需替换“您的位置”即可。
select *
from table1
where (select TO_NUMBER(TESTCOL)
from table2
where "your where") = 1234;
