30

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).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jailu Lee
  • 611
  • 1
  • 8
  • 13
  • Related post - [Why are values stored in an NVARCHAR column sometimes padded with trailing spaces?](https://stackoverflow.com/q/5746143/465053) – RBT Sep 24 '21 at 09:05

2 Answers2

37

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • Of course comparing with `LIKE` does not test for equality, so the next natural question is [How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?](https://stackoverflow.com/a/29108892/1026) – Nickolay Jan 22 '21 at 12:58
7

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138