UNDOCUMENTED FEATURE when SELECT in VARCHAR with trailing whitespace SQL Server

I hope this is an interesting puzzle for an SQL expert out there.

When I run the following query, I would expect it to return no results.

-- Create a table variable Note: This same behaviour occurs in standard tables.

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)

-- Add some test data Note: Without space, space prefix and space suffix

INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

-- SELECT statement that is filtered by a value without a space and also a value with a space suffix

SELECT 
     t.Foo
     , t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar '
AND t.Foo = 'Bar'

The results return a single row:

[Foo]  [About]
Bar    Space Suffix

I need to know more about this behaviour and how I should work around it.

It is also worth noting that LEN(Foo) is odd too, as follows:

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

SELECT 
     t.Foo
     , LEN(Foo) [Length]
     , t.About
FROM @TestResults t

Gives the following results:

[Foo]   [Length]  [About]
Bar     3         No spaces
Bar     3         Space Suffix
 Bar    4         Space prefix

Without any lateral thinking, what do I need to change my WHERE clause to in order to return 0 results as expected?

Solution

The answer is to add the following clause:

AND DATALENGTH(t.Foo) = DATALENGTH('Bar')

Running the following query...

DECLARE @Chars TABLE (CharNumber INT NOT NULL)

DECLARE @CharNumber INT = 0

WHILE(@CharNumber <= 255)
    BEGIN
        INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)

        SET @CharNumber = @CharNumber + 1

    END

SELECT 
    CharNumber
    , IIF('Test' = 'Test' + CHAR(CharNumber),1,0) ['Test' = 'Test' + CHAR(CharNumber)]
    , IIF('Test' LIKE 'Test' + CHAR(CharNumber),1,0) ['Test' LIKE 'Test' + CHAR(CharNumber)]
    , IIF(LEN('Test') = LEN('Test' + CHAR(CharNumber)),1,0) [LEN('Test') = LEN('Test' + CHAR(CharNumber))]
    , IIF(DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber)),1,0) [DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))]
FROM @Chars
WHERE ('Test' = 'Test' + CHAR(CharNumber))
OR ('Test' LIKE 'Test' + CHAR(CharNumber))
OR (LEN('Test') = LEN('Test' + CHAR(CharNumber)))
ORDER BY CharNumber

...produces the following results...

CharNumber  'Test' = 'Test' + CHAR(CharNumber)  'Test' LIKE 'Test' + CHAR(CharNumber)   LEN('Test') = LEN('Test' + CHAR(CharNumber))    DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))
0           1                                   1                                       0                                               0
32          1                                   0                                       1                                               0
37          0                                   1                                       0                                               0

DATALENGTH can be used to test the equality of two VARCHAR, therefore the original query can be corrected as follows:

-- Create a table variable Note: This same behaviour occurs in standard tables.

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)

-- Add some test data Note: Without space, space prefix and space suffix

INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

-- SELECT statement that is filtered by a value without a space and also a value with a space suffix

SELECT 
     t.Foo
     , t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar ' 
AND t.Foo = 'Bar' 
AND DATALENGTH(t.Foo) = DATALENGTH('Bar') -- Additional clause

I also made a function to be used instead of =

ALTER FUNCTION dbo.fVEQ( @VarCharA VARCHAR(MAX), @VarCharB VARCHAR(MAX) ) 
RETURNS BIT 
WITH SCHEMABINDING
AS
BEGIN
    -- Added by WonderWorker on 18th March 2020

    DECLARE @Result BIT = IIF(
        (@VarCharA = @VarCharB AND DATALENGTH(@VarCharA) = DATALENGTH(@VarCharB))

    , 1, 0)

    RETURN @Result

END

..Here is a test for all 256 characters used as trailing characters to prove that it works..

-- Test fVEQ with all 256 characters

DECLARE @Chars TABLE (CharNumber INT NOT NULL)

DECLARE @CharNumber INT = 0

WHILE(@CharNumber <= 255)
    BEGIN
        INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)

        SET @CharNumber = @CharNumber + 1

    END

SELECT 
    CharNumber
    , dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) [fVEQ Trailing Char Test]
    , dbo.fVEQ('Bar','Bar') [fVEQ Same test]
    , dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') [fVEQ Leading Char Test]
FROM @Chars
WHERE (dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) = 1)
AND (dbo.fVEQ('Bar','Bar') = 0)
AND (dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') = 1)