CODEWITHSUNDEEP
sql-serversql-server-2012
Jailu Lee
Jailu Lee

Reputation: 641

Why the SQL Server ignore the empty space at the end automatically?

I wrote a query 

SELECT * FROM Users WHERE UserName = 'admin '

by mistaken typing. But I found that the result is as the same as 

SELECT * FROM Users WHERE UserName = 'admin'

It seems that the empty space at the end is ignored by SQL Server automatically.

Can anybody tell me why?

FYI, the column UserName is of type nvarchar(MAX).

Upvotes: 33

Views: 15523

Answers (2)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

If you are trying like this

SELECT * 
FROM Users 
WHERE UserName = 'admin '

empty spaces are ignored SQL Server. But if you try like this

SELECT * 
FROM Users 
WHERE UserName Like 'admin '

It consider the empty spaces also.

DECLARE @Person1 varchar(50)='admin'
SELECT 1  WHERE @Person1 = 'admin '
SELECT 1  WHERE @Person1 like 'admin '

The result of the above query set is

enter image description here

Upvotes: 9

Ian Preston
Ian Preston

Reputation: 39586

SQL Server is following the ANSI/ISO standard for string comparison.

The article How SQL Server Compares Strings with Trailing Spaces explains this in detail.

SQL Server follows the ANSI/ISO SQL-92 specification... 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.

Also, as explained in the article, if you compare with LIKE you do not get this behaviour.

Upvotes: 40

Related Questions