Comparing strings with trailing spaces

There is all the time confusion in developer’s daily job how exactly is SQL Server handling padding spaces before and after strings in comparison and data persistence. Time to make it more clear now.

Few most important things to remember are:

  1. 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 WHERE and HAVING clause predicates and other Transact-SQL string comparisons.
  2. 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.
  3. The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons.

There is a simple script to check how it works:

You can see that SQL Server handles strings ‘abc’ and ‘abc ‘ as to be the same. The only exception is  LIKE ‘abc %’ where space means search condition that any character can be at the position in searched expression.

How use off SET ANSI_PADDING ON will affect our script? There will no difference because this setting is affecting data at the time of writing but not reading. To check this we should modify our script and save data to a temporary table first:

The core change is that our DataLen is now 3 everywhere and this means that SQL Server stored our data without padding spaces. We see that all rows for LIKE are gone In our result set because compare conditions don’t match.

You should remember two other important things:

  • SET ANSI_PADDING is a deprecated feature for Azure SQL and will be deprecated for SQL Server soon
  • SET ANSI_PADDING should be set to ON always

Leave a Reply

Your email address will not be published. Required fields are marked *