oracle 如何将 NUMBER 与具有前导零的 VARCHAR2 类型数字进行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33918032/
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
How to compare a NUMBER with VARCHAR2 type number having leading zeroes?
提问by JoshKni8
I am trying to compare two columns in two different tables in a database. One column is of type VARCHAR2(4 CHAR) and the other is of type DECIMAL. Although I am pulling these columns into JAVA as a string to do the comparison, I am running into this issue:
我正在尝试比较数据库中两个不同表中的两列。一列的类型为 VARCHAR2(4 CHAR)，另一列的类型为 DECIMAL。虽然我将这些列作为字符串提取到 JAVA 中进行比较，但我遇到了这个问题：
Elements in column (decimal) are: 123, 456, 789
列（十进制）中的元素为：123、456、789
Elements of compared column (varchar) are: 0123, 0456, 0789
比较列（varchar）的元素是：0123、0456、0789
So I want to know if there is a way that I can remove this prefixed 0 as I am pulling it from the database (like some sort of TRIM0()) such that elements from both columns would be the same?
所以我想知道是否有一种方法可以删除这个前缀为 0 的，因为我从数据库中提取它（比如某种 TRIM0()），这样两列中的元素就会相同？
I am trying to avoid handling this column in the JAVA itself to improve performance.
我试图避免在 JAVA 本身中处理此列以提高性能。
采纳答案by Lalit Kumar B
(like some sort of TRIM0()) such that elements from both columns would be the same?
（比如某种 TRIM0()），这样来自两列的元素将是相同的？
Your requirement is about data type conversion, not string manipulation.
您的要求是关于数据类型转换，而不是字符串操作。
Elements might look similar, but it is the data typethat matters. '123'and 123are not the same. They look similar, but they are different data types.
元素可能看起来相似，但重要的是数据类型。'123'并且123不一样。它们看起来相似，但它们是不同的数据类型。
Do not use LTRIMas it would return you a STRING, you will have to convert it to NUMBERagain to avoid implicit data type conversion. It is pointless to return a string, and then use TO_NUMBER again to convert it to number.
不要使用LTRIM，因为它会返回一个STRING，您必须再次将其转换为NUMBER以避免隐式数据类型转换。返回一个字符串，然后再次使用 TO_NUMBER 将其转换为数字是没有意义的。
The correct and efficient way is to use TO_NUMBER. It would convert it into NUMBER in just one step.
正确有效的方法是使用TO_NUMBER。它只需一步即可将其转换为 NUMBER。
For example,
例如，
SQL> SELECT to_number('00123') num FROM dual;
NUM
----------
123
SQL> SELECT * FROM dual WHERE to_number('00123') = 123;
D
-
X
On a side note, if you are sure that always need a NUMBERfrom the column to process, then you must fix it at design level. Storing numbersas character typeis a design flaw, unless there is a specific business need.
附带说明一下，如果您确定始终需要来自列的NUMBER进行处理，那么您必须在设计级别修复它。除非有特定的业务需要，否则将数字存储为字符类型是一个设计缺陷。
回答by Mihail
You can use LTRIM to remove the leading zeroes from the varchar number:
您可以使用 LTRIM 从 varchar 数字中删除前导零：
select ltrim( '0001234', '0' ) from dual;
But I would suggest revisiting your design and comparing number to number.
但我建议重新审视您的设计并将数字与数字进行比较。
回答by Darkmere
You can try casting but I believe this will hurt the performance of the query. And it will prob cause issues if there is ever a letter in one
您可以尝试强制转换，但我相信这会损害查询的性能。如果其中有一封信，它可能会导致问题
where cast(column1 as unsigned) = cast(column2 as unsigned)
