UNDOCUMENTED FEATURE when SELECT in VARCHAR with trailing whitespace SQL Server

Question

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?

when people copy paste.. it not necessarily has to be space. It can be invisible characters too. You may have to use PATINDEX to filter our anything other than Alphabets, Numbers. — Ganesh Chandrasekaran, Mar 17 '20 at 15:13
Does this answer your question? [Like operator and Trailing spaces in SQL Server](https://stackoverflow.com/questions/25162088/like-operator-and-trailing-spaces-in-sql-server) — SMor, Mar 17 '20 at 15:15
For the equals (`=`) operator, trailing spaces are ignored. They are not for *leading* spaces. Therefore `'abc ' = 'abc'` however, `' abc' != 'abc'`. If you are concerned about users inputting leading and trailing spaces in values, you should "cleanse" the value before it is inserted into the database. — Thom A, Mar 17 '20 at 15:16
Microsoft has a KB article about it, does this help? https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces — Tim Mylott, Mar 17 '20 at 15:17
Then it isn't a whitespace, as @GaneshChandrasekaran suggests. Perhaps there are zero-length spaces or line breaks in there. But in your example you aren't trimming, those values are most definiately `' Bar'` and `'Bar '`. — Thom A, Mar 17 '20 at 15:19
@Tim Mylott Brilliant thank you. I will keep an eye on that. — WonderWorker, Mar 17 '20 at 15:21
@Ganesh Chandrasekaran I checked them in a hex editor and they are character 20 (standard space) — WonderWorker, Mar 17 '20 at 15:21
Then `TRIM` (or `LTRIM` and `RTRIM`) would remove them. Show us an example where you are inserting a value with a leading/trailing space, **wrapped** with `TRIM` and it still have a leading/trailing space. If not, then we can't replciate the issue, as it is known functionality that trailing spaces are ignored, and leading aren;t. — Thom A, Mar 17 '20 at 15:23
@WonderWorker I believe space is Asc(32). check the chart here https://ascii.cl/ — Ganesh Chandrasekaran, Mar 17 '20 at 15:44
@Ganesh Chandrasekaran That's correct, however when working with bits and bytes it's most common (and useful due to multiples of 8 i.e. 8 bits in a byte) to think in hexadecimal over decimal. The hex of 32 is 20 i.e. 2 x 16. That's why you tend to see %20 in url and html etc. It's still character 32, but converted to hex. Experiment with memory and binary files and you'll soon see why hex is so valuable. — WonderWorker, Mar 19 '20 at 11:53

WonderWorker · Accepted Answer · 2020-03-19T16:10:26.943

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)

`WHERE t.foo = 'Bar' AND LEN(t.Foo) = LEN('Bar')` should be sufficient. — Olivier Jacot-Descombes, Mar 18 '20 at 19:28
Precisely the reason I asked the question. Run it and see. What we found is that LEN ignores trailing spaces. DATALENGTH does not. — WonderWorker, Mar 19 '20 at 11:49

Steve · Answer 2 · 2021-02-03T00:27:53.810

The reason why trailing whitespace is disregarded in string comparison, is because of the notion of fixed-length string fields, in which any content shorter than the fixed length is automatically right-padded with spaces. Such fixed-length fields cannot distinguish meaningful trailing spaces from padding.

The rationale for why fixed-length string fields even exist, is that they improve performance significantly in many cases, and when SQL was designed it was common for character-based terminals (which usually treated trailing spaces equivalent to padding), reports printed with monospaced fonts (which used trailing spaces for padding and alignment), and data storage and exchange formats (which used fixed-length fields in place of extensive and costly delimiters and complicated parsing logic), to all be oriented around fixed-length fields, so there was a tight integration with this concept at all stages of processing.

When comparing two fixed-length fields of the same fixed length, a literal comparison would of course be possible and would produce correct results.

But when comparing a fixed-length field of a given fixed length, to a fixed-length field of a different fixed length, the desired behaviour would never be to include the trailing spaces in the comparison, since two such fields could never match literally simply by virtue of their differing fixed lengths. The shorter field could be cast and padded to the length of the longer (at least conceptually if not physically), but the trailing space would still then be considered as padding rather than as meaningful.

When comparing a fixed-length field to a variable-length field, the desired behaviour is also probably never to include trailing spaces in the comparison. More complicated approaches which attempt to attribute meaning to trailing spaces in the variable-length side of the comparison, would only come at the cost of slower comparison logic and additional conceptual complexity and potential for error.

In terms of why variable-length to variable-length comparisons ignore trailing spaces, since here spaces can be meaningful in principle, the rationale is probably maintaining consistency in comparison behaviour as when fixed-length fields are involved, and the avoidance of the most common kind of error, since trailing spaces are spurious in databases far more often than they are meaningful.

Nowadays, a database system designed in every respect from scratch would probably forsake fixed-length fields, and probably perform all comparisons literally, leaving the developer to deal explicitly with spurious trailing spaces, but in my experience this would result in extra development effort and far more frequent error than the current arrangement in SQL, where errors in program logic involving the silent disregard of trailing spaces usually only occurs when designing complex string-shredding logic to be used against un-normalised data (which is a kind of data that SQL is specifically not optimised for handling).

So to be clear, this is not an undocumented feature, but a prominent feature that exists by design.

Great explanation Steve. I wonder what's going on under the hood, I wonder if the LEN function reads each character in a VARCHAR, starting from right and moving left each time until it reaches a non-space character, but DATALENGTH might read from the left but stop when it reaches a character that represents the end of the string. I don't suppose you would know how to find out would you? Either way, thank you for your brilliant answer. — WonderWorker, Feb 03 '21 at 08:13
@WonderWorker, the `DATALENGTH` differs in that it returns the byte-length of the string. For Unicode characters, each logical character may be comprised of multiple bytes. `LEN` returns the logical string length, including the disregard of trailing spaces. The two functions are not direct complements of each other. I don't know the actual implementations, but I expect strings are stored with a length, and Datalength returns this raw value, whilst Len takes the value but then works backwards from the right deducting any series of spaces until it reaches a non-space character. — Steve, Feb 03 '21 at 12:22

Olivier Jacot-Descombes · Answer 3 · 2020-03-19T14:00:06.853

If you change the query to

SELECT 
     Foo
     , About
     , CASE WHEN Foo LIKE 'Bar ' THEN 'T' ELSE 'F' END As Like_Bar_Space
     , CASE WHEN Foo LIKE 'Bar'  THEN 'T' ELSE 'F' END As Like_Bar
     , CASE WHEN Foo =    'Bar ' THEN 'T' ELSE 'F' END As EQ_Bar_Space
     , CASE WHEN Foo =    'Bar'  THEN 'T' ELSE 'F' END As EQ_Bar
FROM @TestResults

it gives you a better overview, as you see the result of the different conditions separately:

Foo     About         Like_Bar_Space   Like_Bar   EQ_Bar_Space   EQ_Bar
------  ------------  ---------------  ---------  -------------  ------
Bar     No spaces      F                T          T              T
Bar     Space Suffix   T                T          T              T
 Bar    Space prefix   F                F          F              F

It looks like equals = ignores trailing spaces in both searched string and pattern. LIKE, however, does not ignore the trailing space in the pattern but ignores an extra trailing space in the searched string. Leading spaces are never ignored.

I don't know how wrong entries got in there, but you can fix them with

UPDATE @TestResults SET Foo = TRIM(Foo)

You can make a trailing space sensitive test with:

WHERE t.Foo + ";" = pattern + ";"

You can make a trailing space insensitive test with:

WHERE RTRIM(t.Foo) = RTRIM(pattern)

You were wrong to assume that trailing spaces in the values needed fixing. They are perfectly decent values, so by attempting to fix them by trimming them away, you have unfortunately broken them — WonderWorker, May 11 '22 at 20:34
@WonderWorker, in many cases leading and trailing spaces are not desired. E.g., person names and addresses. — Olivier Jacot-Descombes, May 12 '22 at 11:12

UNDOCUMENTED FEATURE when SELECT in VARCHAR with trailing whitespace SQL Server

3 Answers3

Linked