INF: Comparison of Strings Containing Trailing Spaces

• Microsoft SQL Server version 7.0

SUMMARY

With ANSI_PADDING ON, SQL Server 7.0 follows the ANSI Standard for string comparison when comparing strings that are only differentiated by trailing spaces. The ANSI standard is for the shorter string to be padded to the length of the longer string for comparison. Because of this, two strings differing only in the number of trailing spaces will not be viewed as being unique.

In a table created with ANSI_PADDING ON in SQL 7.0, the following strings will all be treated equally for comparison purposes:

PaddedColumn

------------
'abc'
'abc '
'abc  '
'abc    '

etc... 

MORE INFORMATION

Here are two examples of where this will affect SQL Server behavior:

• SQL Server 7.0 will not allow you to treat strings as being unique if they are only differentiated by trailing spaces. Therefore, a Primary Key or Unique Constraint created on a char or varchar column treats strings (like the preceding examples) as being identical and will prevent you from inserting more than one of them. You will receive one of the follow errors, depending on the constraint defined:

  Server: Msg 2627, Level 14, State 1
  Violation of PRIMARY KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'table_name'.
  The statement has been terminated.

  Server: Msg 2627, Level 14, State 2
  Violation of UNIQUE KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'table_name'.
  The statement has been terminated.

  To enforce the Primary Key or Unique Constraints in tables created with ANSI_PADDING ON, string comparison is done by padding the shorter strings with spaces to the length of the longer string, thus making each string in the preceding list the same.



• A query containing a WHERE clause that refers to a column containing strings with padded spaces will not restrict rows to those with the amount of trailing spaces as in the WHERE clause. For example, the following query returns all rows containing 'a' in the PaddedColumn field:

  
  SELECT * from table_name WHERE PaddedColumn = 'a ' 

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo