where子句中忽略空白字符 [英] Blank character ignored in where clause
问题描述
我已完成以下操作-
create table test (col char(10));
insert into test values ('hello');
select * from test where col = 'hello'
我建议上面的内容不应该返回任何结果，因为'col'是10个字符，它将用空格正确填充，因此与'hello'进行比较将不会返回结果.但是我得到了结果.谁能解释一下?我正在使用11gR2
I have been suggested that the above should not return any result as 'col' is 10 chars, it will be right padded with blanks, so comparing with 'hello' will not return result. But I am getting the result. Can anyone please explain this? I am using 11gR2
推荐答案
查看
文本文字具有同时具有 Text literals have properties of both the 和空白比较语义的文档声明: 使用空格填充的语义，如果两个值的长度不同，则Oracle首先将空格添加到较短的空格的末尾，以使它们的长度相等.然后，Oracle逐个字符地比较值，直到第一个不同的字符为止.在第一个不同位置处具有较大字符的值被认为较大.如果两个值没有不同的字符，则认为它们相等.该规则意味着两个值仅在尾随空白数上不同时相等.只有当比较中的两个值都是数据类型 With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype 由于比较的左侧是 Since the left-hand side of the comparison is a 您可以在简单的示例中看到这一点: You can see this in the simple example: 更新: [TL; DR]至少从Oracle 7(1992年发布)开始，此行为在Oracle的所有版本中均已出现.我停止搜索已有二十多年历史的发行版文档，但我希望您会发现大多数(所有?)版本中都存在这种情况. [TL;DR] This behaviour has appeared in all versions of Oracle since at least Oracle 7 (released in 1992). I stopped searching for the documentation on releases over two decades old but I expect that you will find that this has been the behaviour in most (all?) versions. 以下是各种版本的文档: Here is the documentation for the various versions: 这篇关于where子句中忽略空白字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！CHAR和VARCHAR2数据类型的属性:
CHAR and VARCHAR2 datatypes:
CHAR.
CHAR by comparing them using blank-padded comparison semantics.
CHAR ， NCHAR 的表达式，文本文字或 USER 功能.
CHAR, NCHAR, text literals, or values returned by the USER function.CHAR(10)，右侧是文本文字，因此使用空白填充的比较语义，并且'hello ' = 'hello'是true.CHAR(10) and the right-hand side is a text literal then blank-padded comparison semantics are used and 'hello ' = 'hello' is true.SELECT * FROM DUAL WHERE 'hello ' = 'hello';
