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

by

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.

More Related Contents:

  1. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  2. LocalDB deployment on client PC
  3. How to check if a column exists in a SQL Server table
  4. Auto increment primary key in SQL Server Management Studio 2012
  5. XML Server XML performance optimization
  6. Adding a query hint when calling Table-Valued Function
  7. How to enable bulk permission in SQL Server
  8. Sequence as default value for a column
  9. Last executed queries for a specific database
  10. Avg of float inconsistency
  11. Dynamically assign filename to excel connection string
  12. SQL Server Configuration Manager cannot be found
  13. Joining multiple tables returns NULL value
  14. Is it possible to use user defined aggregates (clr) with window functions (over)?
  15. How to pivot unknown number of columns & no aggregate in SQL Server?
  16. “No backupset selected to be restored” SQL Server 2012
  17. Restore a SQL Server 2000 backup on SQL Server 2012
  18. Create a view with ORDER BY clause
  19. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  20. Possible to store value of one select column and use it for the next one?
  21. How can I get the sum of multiple datetime values?
  22. Running an SSIS Package using dtexec
  23. Use of Begin / End Blocks and the Go keyword in SQL Server?
  24. How do you determine what SQL Tables have an identity column programmatically
  25. Ampersand (&) operator in a SQL Server WHERE Clause
  26. how to write number to word function in sql server [closed]
  27. Milliseconds wrong when converting from XML to SQL Server datetime
  28. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  29. “If not exists” fails on SQL CE
  30. Generate MD5 hash string with T-SQL

Leave a Comment