How Does SQL Server's WHERE Clause Handle Trailing Spaces in String Comparisons?

SQL WHERE Clause Matching Values with Trailing Spaces

In SQL Server 2008, consider a table named Zone with a column ZoneReference as the primary key. Executing the query SELECT '"' ZoneReference '"' AS QuotedZoneReference FROM Zone WHERE ZoneReference = 'WF11XU' unexpectedly returns "WF11XU " with a trailing space.

This arises from SQL Server's adherence to the ANSI/ISO SQL-92 specification, whereby strings are padded for comparisons and treated as equivalent when lengths match. Consequently, 'abc' and 'abc ' are deemed equal.

This behavior also affects WHERE clause predicates, where trailing spaces are ignored. However, the exception is the LIKE predicate, which compares values without padding when a trailing space is present on the right side of the expression.

To suppress trailing spaces in your comparisons, consider using the TRIM() function in your query or data manipulation statements.

The above is the detailed content of How Does SQL Server's WHERE Clause Handle Trailing Spaces in String Comparisons?. For more information, please follow other related articles on the PHP Chinese website!