Why is there is a difference between equality comparison between equal operator and like operator ?

Question

SELECT au_lname, au_fname  
FROM authors
WHERE au_lname = 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------
Green                                    Marjorie

SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------

Could anyone please explain me why is the second query is not returning the expected row?

Answer 1

It depends on the padding stored.

LIKE 'Green%' and = 'Green' will both find 'Green' whereas LIKE 'Green ' will not find 'Green' but LIKE 'Green%' will. And '%Green%' will find ' Green ', 'Mr. Green', ' Green', 'Green ', ' Green Ghost' and 'Greentree'

NOTE: most sql will actually match the case on - as well as in: 'GreeN', 'green' 'GREEN' all match ='Green'.

Answer 2

It's a quirk of the standards apparently. Mise well be explicit:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

See here, here and here.

Bizarre - given the terms "equal" and "like" I would have expected the latter to be the more liberal.

Answer 3

your char(8) literal 'Green ' value is automatically converted to the same data type as au_lname column, which is most likely varchar(), so the trailing space is removed in the conversion. This is probabily the optimizer at work trying to make the data types the same, so an index can be used.

SELECT au_lname, au_fname  
FROM authors
WHERE au_lname = 'Green ' --auto conversion to varchar() is 'Green'

using LIKE, there is no auto conversion from the CHAR(8) 'Green ' value, so no matches are found. Most likely, this is done, so the pattern matching capabilities are preserved. if you want to search for LIKE 'xyz% ' that is quite different than LIKE 'xyz%'

SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE 'Green ' --no conversion, remains CHAR(8) 'Green '