Why "=" and "LIKE" behave the opposite way they should?

I discovered a problem on my procedure and finally the problem was found. When trying to ask documents with configuration name ‘Default’ these two queries give two different results:

SELECT DC.ConfigurationName, DC.ConfigurationID
FROM DocumentConfiguration DC
WHERE DC.ConfigurationName='Default'

Results:

That lower ‘Default’ has a trailing space, ASCII character 32.

This query gives only the exact result I needed:

SELECT DC.ConfigurationName, DC.ConfigurationID
FROM DocumentConfiguration DC
WHERE DC.ConfigurationName LIKE 'Default'

Results:

I understood = should give only the exact match while LIKE should be used with wildcards. Why those two queries seem to work the opposite of how they should?

I fixed the procedure so that it now uses LIKE instead of = and the whole procedure works correctly. I did not expect this to work, because = should only give an exact match.

>Solution :

Likely you have trailing spaces on the end of one of the values. When this happens, the = operator treats them both as matches, and the like operator does not, per ANSI standards according the docs.

More info here: https://learn.microsoft.com/en-US/sql/t-sql/language-elements/string-comparison-assignment?view=sql-server-ver16#remarks

The SQL Server Database Engine follows the ANSI/ISO SQL-92
specification (Section 8.2, Comparison Predicate, 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,
the Database Engine doesn’t 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 predicate doesn’t violate the
section of the ANSI SQL-92 specification mentioned earlier.