SQL WHERE clause matching values with trailing spaces

by

That’s the expected result: in SQL Server the = operator ignores trailing spaces when making the comparison.

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.

Source

More Related Contents:

  1. Adding multiple parameterized variables to a database in c#
  2. SqlConnection.Close() inside using statement
  3. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  4. Is SqlConnection.Close() need inside a Using statement?
  5. How can I round a value in SQL
  6. How do I update a value in C# and SQL Server? [closed]
  7. C# Data Connections Best Practice?
  8. How can I schedule a job to run a SQL query daily?
  9. Reset identity seed after deleting records in SQL Server
  10. Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints
  11. How do I connect to an MDF database file?
  12. Getting return value from stored procedure in C#
  13. SQL error: Incorrect syntax near the keyword ‘User’
  14. Query extremely slow in code but fast in SSMS
  15. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  16. Code to validate SQL Scripts
  17. How to access ssis package variables inside script component
  18. SQLException : String or binary data would be truncated
  19. Creating a database programmatically in SQL Server
  20. Generate class from database table
  21. Invalid column name sql error
  22. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  23. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  24. What is passing parameters to SQL and why do I need it?
  25. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  26. How to pass a null variable to a SQL Stored Procedure from C#.net code
  27. How to insert data into SQL Server
  28. How do I change db schema to dbo
  29. How to use String property as primary key in Entity Framework
  30. Generating sql code programmatically

Leave a Comment