在Oracle上选择字符串作为数字 [英] Select string as number on Oracle

问题描述

我发现了这种奇怪的行为，我为此感到震惊...任何人有任何想法吗?

I found this odd behavior and I'm breaking my brains with this... anyone has any ideas?

Oracle 10g: 我有两个不同的表，都具有名为 Varchar2(10)的名为" TESTCOL "的列，不能为空.

Oracle 10g: I have two different tables, both have this column named "TESTCOL" as Varchar2(10), not nullable.

如果我在 table1 上执行此查询，则会得到正确的结果:

If I perform this query on table1, i get the proper results:

select * from table1 where TESTCOL = 1234;

请注意，我不是专门放置"1234" ...不是错字，而是动态生成的查询，因此我将尽量不进行更改(至少在不久的将来不会更改) ).

Note that 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).

但是，如果我在 table2 上运行相同的查询，则会收到以下错误消息:

But, if I run the same query, on table2, I get this error message:

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?

谢谢.

推荐答案

如果TESTCOL包含非数字，那么在将TESTCOL条目转换为数字时，Oracle可能会遇到问题.因为，它在内部执行的操作是这样的:

If TESTCOL contains non-numbers, then Oracle might run into problems when converting TESTCOL entries to numbers. Because, what it does internally, is this:

select * from table1 where TO_NUMBER(TESTCOL) = 1234;

如果您确定不能将1234表示为VARCHAR文字，请尝试使用此方法，以便比较varchar值，而不是数字值:

If you're so sure that 1234 cannot be expressed as a VARCHAR literal, then try this instead, in order to compare varchar values, rather than numeric ones:

select * from table1 where TESTCOL = TO_CHAR(1234);

这篇关于在Oracle上选择字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！