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

Question

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?

Answer 1

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)

Answer 2

This is old, and already answered, but I was struggling with this as a result of comparison in a JOIN condition too. While it is true that SQL Server ignores trailing spaces, it doesn't ignore leading spaces. Therefore for my JOIN condition, I compared forwards and backwards (using the reverse function) and that gave me a more accurate set of results.

In the example below, I want rows with a trailing space in one table to only match rows with a trailing space in the JOINed table. The REVERSE function helped with this.

DECLARE @DbData AS TABLE (
    Id INT,
    StartDate DATETIME,
    OrgName VARCHAR(100)

)

DECLARE @IncomingData AS TABLE (
    Id INT,
    StartDate DATETIME,
    OrgName VARCHAR(100)
)

INSERT INTO @DbData (Id, StartDate, OrgName) 
SELECT 1, CAST('1 Jan 2022' AS DATE), 'Test ' UNION ALL 
SELECT 2, CAST('1 Jan 2022' AS DATE), 'Test' UNION ALL
SELECT 3, CAST('1 Jan 2022' AS DATE), 'Other Test' UNION ALL
SELECT 4, CAST('1 Jan 2022' AS DATE), 'Other Test ' 

INSERT INTO @IncomingData (Id, StartDate, OrgName)
SELECT 1, CAST('1 Jan 2022' AS DATE), 'Test ' UNION ALL 
SELECT 2, CAST('1 Jan 2022' AS DATE), 'Test' UNION ALL
SELECT 3, CAST('1 Jan 2022' AS DATE), 'Other Test' 

SELECT '~' + dd.OrgName + '~', '~' + id.OrgName + '~', * 
FROM @DbData dd
JOIN @IncomingData id ON id.StartDate = dd.StartDate
    AND dd.OrgName = id.OrgName
    AND REVERSE(dd.OrgName) = REVERSE(id.OrgName) -- Try the query with and without this line to see the difference it makes