Blank character ignored in where clause

问题:

I have done the following -

    create table test (col char(10)); 
    insert into test values ('hello');
    select * from test where col = 'hello'   

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

回答1:

Looking at the Oracle Documentation on literals:

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

• Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.

and the documentation of blank-padded comparison semantics states:

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 CHAR, NCHAR, text literals, or values returned by the USER function.

Since the left-hand side of the comparison is a CHAR(10) and the right-hand side is a text literal then blank-padded comparison semantics are used and 'hello ' = 'hello' is true.

You can see this in the simple example:

SELECT * FROM DUAL WHERE 'hello    ' = 'hello';

Update:

[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:

• Oracle 12c Text Literals & blank-padded semantics
• Oracle 11g Text Literals & blank-padded semantics
• Oracle 10gR2 Text Literals & blank-padded semantics
• Oracle 9 Text Literals & blank-padded semantics
• Oracle 8 Text Literals & blank-padded semantics
• Oracle 7 Text Literals

回答2:

Oracle uses "blank padded" comparison semantics in this example because one of the expressions is datatype CHAR and the other expression is a string literal.

There are rules about when Oracle uses blank padded comparison and non-padded comparison semantics. It's covered in the Oracle documentation somewhere.

---

The suggestion were given (that Oracle would not return a result because the CHAR column would be right padded with spaces) was erroneous.

回答3:

You got the correct answer already. Now, you may wonder "how can I force a comparison where the comparison string 'hello' is interpreted as VARCHAR2, so that the query will return no rows?"

The answer is, you must force 'hello' to be seen as VARCHAR2. Like so:

... where col = cast ('hello' as VARCHAR2(10))

(note that the syntax for the cast function requires you to specify the length).

Even though you specify the length of 10, since the cast is as VARCHAR2, the result is really just 5 characters - there is no blank padding of VARCHAR2 values.