oracle 在oracle中将数字作为字符串进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1529745/
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
Comparing numbers as string in oracle
提问by Priyank
I have a comparison in Oracle for two numbers. 010 and 10. They are equal as long as numeric equality is concerned; however, I need to compare them as String. I tried to_char but it doesn't work.
我在 Oracle 中对两个数字进行了比较。010 和 10。只要涉及数字相等，它们就相等；但是，我需要将它们作为字符串进行比较。我试过 to_char 但它不起作用。
Are there any other functions which would let me do exact comparison of numeric value as string?
有没有其他函数可以让我将数值作为字符串进行精确比较？
------------To clarify everyone's doubt---------------------
------------为大家解惑------------
I have three columns address1 address2 and address3
I want to compare ONLY the digits in the concatenation of all three. so for example if values are as follows:
address1 = 01 park avenue
address2 = 20 golden gate
address3 = null
我有三列 address1 address2 和 address3 我只想比较所有三列连接中的数字。例如，如果值如下：
address1 = 01 park avenue
address2 = 20 gold gate
address3 = null
then I would like to compare data in table to see if any of the addresses' concatenated value comes out to be 0120
然后我想比较表中的数据，看看是否有任何地址的连接值是 0120
However right now it's equalizing 120 also with 0120 which I do not desire.
然而，现在它也在用我不想要的 0120 来平衡 120。
Data is extracted and concatenated, so not stored in a type of column. All I need is to ensure, that these numbers are compared "EXACTLY" and not as numbers.
数据被提取和连接，所以不存储在列的类型中。我所需要的只是确保将这些数字“完全”进行比较，而不是作为数字进行比较。
Please suggest.
请建议。
Cheers
干杯
回答by Ron Savage
You don't really have an option here - you are either comparing strings - or numbers.
您在这里没有真正的选择 - 您要么比较字符串 - 要么数字。
The "strings":
"10"
"010"
"0010"
"00010"
when converted to an Integer all = 10.
当转换为整数时，all = 10。
If you start with an integer 10, you have no way of knowing how many leading zeros the "string" version of it should have. So store them all as strings, or compare them as numbers - meaning "10" = "010" = "0010" = 10.
如果从整数 10 开始，则无法知道它的“字符串”版本应该有多少个前导零。因此，将它们全部存储为字符串，或将它们作为数字进行比较 - 意思是“10”=“010”=“0010”=10。
回答by Nick Pierpoint
Is this what you're after?
这是你追求的吗？
Set up some example data:
设置一些示例数据：
create table address as
select
'01 park avenue' address1,
'20 golden gate' address2,
'30 test' address3
from
dual;
insert into address
select
'01 park avenue' address1,
'20 golden gate' address2,
null address3
from
dual;
insert into address
select
'01 park avenue' address1,
'20 golden gate' address2,
null address3
from
dual;
commit;
Here's a query that will find 'duplicates' by ordering by the concatenated number string. We extract the numbers from the address using regexp_replace on the address concatenation.
这是一个查询，它将通过按连接的数字字符串排序来查找“重复项”。我们在地址连接上使用 regexp_replace 从地址中提取数字。
select
address1 || address2 || address3 address_concat,
regexp_replace(address1 || address2 || address3, '[^[:digit:]]')
address_numbers_only
from
address
order by
address_numbers_only;
If you're looking for matches to a specific address - try something like this:
如果您正在寻找特定地址的匹配项 - 请尝试以下操作：
select
*
from
address
where
regexp_replace(address1 || address2 || address3, '[^[:digit:]]') =
regexp_replace(:v_address1 ||
:v_address2 ||
:v_address3, '[^[:digit:]]');
For example:
例如：
select
*
from
address
where
regexp_replace(address1 || address2 || address3, '[^[:digit:]]') =
regexp_replace('01 park avenue' ||
'20 golden gate' ||
null, '[^[:digit:]]');
-- returns...
ADDRESS1 ADDRESS2 ADDRESS3
01 park avenue 20 golden gate
01 park avenue 20 golden gate
回答by APC
The one and only correct way to check for exact matches would be
检查完全匹配的唯一正确方法是
select whatever
from addresses
where address1 = '01'
and address2 = '20'
and address3 is null;
(Substituting bind variables or other columns for the hardcoded values to taste).
（将绑定变量或其他列替换为要品尝的硬编码值）。
The fact that you are ignoring this obvious solution suggests you have some motive for comparing concatenated strings, which you have not yet explained.
您忽略了这个显而易见的解决方案这一事实表明您有一些比较串联字符串的动机，而您尚未对此进行解释。
Matching by concatenation is troublesome, as you are discovering. It works providing all elements are populated and of a fixed length. Once we allow nulls or variable length values we are doomed. None of the following ought to match on the basis of equality of elements, but lo!through the magic of concatenation they do:
正如您所发现的那样，通过串联进行匹配很麻烦。如果所有元素都填充并且长度固定，它就可以工作。一旦我们允许空值或可变长度值，我们就注定失败。根据元素相等，以下任何一项都不应该匹配，但是瞧！通过连接的魔力，他们做到了：
SQL> select * from dual
2 where 1||23 = 12||3
3 /
D
-
X
SQL> select * from dual
2 where 1||null||2 = 1||2||null
3 /
D
-
X
SQL>
SQL> select * from dual
2 where 123||null||null = 1||2||3
3 /
D
-
X
SQL>
The workaround for this problem is to explicitly demarcate the elements in the concatenated string. For instance if we separate the elements in that last example with tildes we no longer get a match...
此问题的解决方法是显式划分连接字符串中的元素。例如，如果我们用波浪号分隔最后一个示例中的元素，我们将不再匹配...
SQL> select * from dual
2 where 123||'~'||null||'~'||null = 1||'~'||2||'~'||3
3 /
no rows selected
SQL>
回答by asalamon74
You have a string field:
你有一个字符串字段：
select '010' str from dual
The following select will return 1 row:
以下选择将返回 1 行：
select * from (select '010' str from dual) where str=10
The following select will return no rows:
以下选择将不返回任何行：
select * from (select '010' str from dual) where str='10'
So even if the field is a string if you just write =10in the where clause Oracle will compare them as numbers. If you write ='10'Oracle will compare them as strings.
因此，即使该字段是字符串，如果您只是=10在 where 子句中写入，Oracle 也会将它们作为数字进行比较。如果你写='10'Oracle 会将它们作为字符串进行比较。
回答by Jaskirat
Are the numbers stored as varchars in the db? If the numbers are stored in integer variables then 010 will be same as 10.
数字是否以 varchars 形式存储在数据库中？如果数字存储在整数变量中，则 010 将与 10 相同。
SELECT 010 FROM DUAL
will return 10. This means that once you have stored any number with leading zeros as an integer you lose the leading zeros. You can't get back what you have lost.
将返回 10。这意味着一旦您将任何带有前导零的数字存储为整数，您就会丢失前导零。失去的东西，你是找不回来的。
Maybe I have understood you wrongly, can you rephrase your question?
也许我理解错了，你能改写一下你的问题吗？
