Can we stop SQL Server EXCEPT from ignoring trailing spaces in values

I am auditing values in 2 identical structure tables. The T-SQL EXCEPT statement is ignoring the trailing space on a value in one table, so the values don't match, but also do not show up in our audit.

I have tried searching for ways to change how SQL is comparing the columns. I did something similar to ensure it was case sensitive, but couldn't find something that would make it include the white space/padding in the field value.

Example data would have the value in MyTable as "Product Name ", while the RemoteTable has the value "Product Name".

To quickly reproduce, here is a slimmed down version of what I'm doing now:

DECLARE @SampleLocal TABLE(ProductName varchar(50))
DECLARE @RemoteTable TABLE(ProductName varchar(50))

INSERT INTO @SampleLocal (ProductName) VALUES ('Product Name')
INSERT INTO @RemoteTable (ProductName) VALUES ('Product Name ')

SELECT ProductName COLLATE SQL_Latin1_General_CP1_CS_AS ProductName  
FROM @SampleLocal

EXCEPT

SELECT ProductName COLLATE SQL_Latin1_General_CP1_CS_AS ProductName  
FROM @RemoteTable

This currently returns no results, showing that the values are the same. But the value in the second table has a space at the end.

I would expect to get a result back that has "Product Name"

When I needed to compare things with case sensitivity I was able to add

COLLATE SQL_Latin1_General_CP1_CS_AS

Is there something similar that would show the value being different because of the blank space?

Solution

According to this article (https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-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.

This behavior is intended.

You can use a slower method to achieve what you wanted:

SELECT innerItems.ProductName
FROM
(
    SELECT DATALENGTH(ProductName) as realLength, ProductName  COLLATE SQL_Latin1_General_CP1_CS_AS as ProductName 
    FROM @SampleLocal
    EXCEPT
    SELECT DATALENGTH(ProductName) as realLength, ProductName  COLLATE SQL_Latin1_General_CP1_CS_AS as ProductName
    FROM @RemoteTable
) innerItems

Comparing the values and real lengths together does the magic here. (The len method would give the 'wrong' result in this case)