'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 1:[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)
Solution 2:[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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | akos.pinter |
Solution 2 | Mr Moose |