How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?

by

From the docs on LEN (Transact-SQL):

Returns the number of characters of the specified string expression, excluding trailing blanks. To return the number of bytes used to represent an expression, use the DATALENGTH function

Also, from the support page on How SQL Server Compares Strings with Trailing Spaces:

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.

Update: I deleted my code using LIKE (which does not pad spaces during comparison) and DATALENGTH() since they are not foolproof for comparing strings

This has also been asked in a lot of other places as well for other solutions:

More Related Contents:

  1. What is the difference between varchar and nvarchar?
  2. What are the main performance differences between varchar and nvarchar SQL Server data types?
  3. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  4. SQL Server Text type vs. varchar data type [closed]
  5. Using varchar(MAX) vs TEXT on SQL Server
  6. How to split a comma-separated value to columns
  7. What is the use of GO in SQL Server Management Studio & Transact SQL?
  8. Confused about UPDLOCK, HOLDLOCK
  9. Retrieve column definition for stored procedure result set
  10. Change Notification with Sql Server 2008
  11. How do you create a yes/no boolean field in SQL server?
  12. Connecting to MS SQL Server with Windows Authentication using Python?
  13. Using COALESCE function to make values separated with commas
  14. TSQL Email Validation (without regex)
  15. How to find the port for MS SQL Server 2008?
  16. How to convert number of minutes to hh:mm format in TSQL?
  17. SQL Server Group by Count of DateTime Per Hour?
  18. Temporarily disable all foreign key constraints
  19. How to import a DBF file in SQL Server
  20. SQL Server Dependencies
  21. Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?
  22. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  23. Date range overlapping check constraint
  24. “Prevent saving changes that require the table to be re-created” negative effects
  25. Convert UTC Milliseconds to DATETIME in SQL server
  26. freeTDS not using its config
  27. @@ERROR and/or TRY – CATCH
  28. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  29. How to make a passthrough / passthru query editable?
  30. Tool for Scripting Table Data

Leave a Comment