CODEWITHSUNDEEP
sql-servert-sql
scottm
scottm

Reputation: 28703

What's the deal with trailing white space in TSQL queries?

For example, if I have this data:

CREATE TABLE FooBar ( Name varchar(16) )

INSERT FooBar SELECT '[email protected]'

The following queries don't return what I would expect:

SELECT * FROM FooBar WHERE Name = '[email protected]       '  -- Returns the row

SELECT * FROM FooBar WHERE Name LIKE '[email protected] '  -- Nothing Returned

SELECT * FROM FooBar WHERE Name = ' [email protected]' -- Nothing Returned

Why does = (which I assume means exactly equals) with extra space at the end return data, while a LIKE does not?

Upvotes: 4

Views: 1037

Answers (1)

Erre Efe
Erre Efe

Reputation: 15557

Seing the standard it depends on padding (The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them)

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 also how SQL Server compares strings with trailing spaces

Upvotes: 5

Related Questions