sql-server - SQL 查询中的尾随空格是怎么回事？

Question

例如，如果我有以下数据:

CREATE TABLE FooBar ( Name varchar(16) )

INSERT FooBar SELECT 'test@test.com'

以下查询不会返回我期望的结果:

SELECT * FROM FooBar WHERE Name = 'test@test.com       '  -- Returns the row

SELECT * FROM FooBar WHERE Name LIKE 'test@test.com '  -- Nothing Returned

SELECT * FROM FooBar WHERE Name = ' test@test.com' -- Nothing Returned

为什么 = (我认为这意味着完全相等)在末尾有额外的空格返回数据，而 LIKE 则不会？

Answer 1

看标准，它取决于填充(ANSI标准要求对比较中使用的字符串进行填充，以便在比较之前它们的长度匹配)

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.

另请参阅how SQL Server compares strings with trailing spaces