I am using SQL Developer over a backend Oracle DB. I have a record where the buyer name is Pete Hansen.
Why I try
select * from data1 where buyer = 'Pete Hansen';
I get the result, no problem. However, when I use the following, I do not get any results:
select * from data1 where buyer like 'Pete Hansen';
Also, when I try the following, I do not get any results
select * from data1 where buyer like 'Pete Hans_n';
but the following works well:
select * from data1 where buyer like 'Pete Hans_n%';
Could you please help me understand? Thanks in advance.
Advertisement
Answer
Your buyer column seems to be defined as char; you can see the issue reproduced in this db<>fiddle, but not when the column is varchar2.
The documentation for character comparison explains the difference between blank-padded or nonpadded comparison semantics. When you compare them with =, because both the column and the string literal are `char, blank-padded semantics are used:
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. … 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 data type CHAR, NCHAR, text literals, or values returned by the USER function.
When the column is `varchar2 then nonpadded semantics are used:
With nonpadded semantics, … If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.
LIKE works differently. Only your final pattern with % matches, because that is allowing for the trailing spaces in the char value, while the other two patterns do not. With the varchar2 version there aren’t any trailing spaces to the other two patterns also match.
It’s unusual to need or want to user char columns; varchar2 is more usual. Tom Kyte opined on this many years ago.