Text Equality: Trailing Spaces Can Compare as Equal

Text equality can treat trailing spaces as padding even when the two stored strings have different byte lengths. I separate matching text from exact representation. A binary collation alone does not remove the comparison-padding rule.

Expose the stored difference before comparing

The first pair compares AB with AB followed by one space. Both values use the same explicit nvarchar type. Their expected byte lengths are four and six.

The equality expression uses a named BIN2 collation on both sides. Its expected TextEquals result is True. The shorter character value is padded for this comparison.

The ExactBytesEqual result is expected to be zero. That expression requires both equal byte lengths and equal binary representations. The extra stored space therefore remains a real distinction.

I display byte lengths beside the values because a grid can hide a trailing space. An apparently identical label does not establish identical input bytes. The length columns make the representation difference reviewable.

WITH Inputs AS
(
    SELECT CaseId,LeftText,RightText FROM (VALUES
        (1,CAST(N'AB' AS nvarchar(8)),CAST(N'AB ' AS nvarchar(8))),
        (2,CAST(N'AB' AS nvarchar(8)),CAST(N'AB' AS nvarchar(8))),
        (3,CAST(N'AB' AS nvarchar(8)),CAST(N'ab' AS nvarchar(8))),
        (4,CAST(N'' AS nvarchar(8)),CAST(N' ' AS nvarchar(8))),
        (5,CAST(NULL AS nvarchar(8)),CAST(N'' AS nvarchar(8)))
    ) AS v(CaseId,LeftText,RightText)
)
SELECT CaseId,LeftText,RightText,DATALENGTH(LeftText) AS LeftBytes,DATALENGTH(RightText) AS RightBytes,
    CASE WHEN LeftText COLLATE Latin1_General_100_BIN2=RightText COLLATE Latin1_General_100_BIN2 THEN 'True'
         WHEN LeftText COLLATE Latin1_General_100_BIN2<>RightText COLLATE Latin1_General_100_BIN2 THEN 'False'
         ELSE 'Unknown' END AS TextEquals,
    CASE WHEN LeftText IS NULL OR RightText IS NULL THEN NULL
         WHEN DATALENGTH(LeftText)=DATALENGTH(RightText)
          AND CONVERT(varbinary(16),LeftText)=CONVERT(varbinary(16),RightText) THEN 1 ELSE 0 END AS ExactBytesEqual
FROM Inputs ORDER BY CaseId;

Keep case matching separate from space padding

The second pair contains two identical AB strings. Both byte lengths are four, text equality is True, and exact byte equality is one. It provides a positive identity control.

The third pair compares AB with lowercase ab. Under the explicit BIN2 rule, expected text equality is False. Exact byte equality is also zero despite equal lengths.

That row isolates a different source of inequality. Case matching comes from the selected collation. Trailing-space comparison padding follows a separate character-comparison rule.

Changing a database’s default collation is unnecessary for this demonstration. The matching choice belongs to each expression. It lets this example hold letter-case behavior constant while examining padding.

Empty text and a space can compare as equal

The fourth pair compares an empty string with one ordinary space. Its expected byte lengths are zero and two. Text equality is True while exact byte equality is zero.

A blank display could therefore represent several stored values. Empty content and one stored space are different representations. A text equality predicate can intentionally consider them equivalent.

The missing-input pair compares SQL NULL with empty text. Its expected text comparison is Unknown rather than True or False. The exact comparison deliberately returns NULL because an input is missing.

I do not replace that missing state with empty text in this example. Doing so would add a source-normalization policy. The demonstration keeps stored representation, matching and missingness separate.

Choose an identity rule suited to the application

A label lookup may legitimately use ordinary text equality. A payload-integrity check may require an exact representation. Those requirements should be stated before choosing the comparison expression.

For these bounded same-type values, the exact test checks the full binary value and its byte length. Equal length alone is insufficient. Two different strings can consume the same number of bytes.

LIKE behaves differently when the pattern contains trailing spaces. This article demonstrates equality only. It does not claim every string predicate applies the same padding rules.

Read text equality beside both byte lengths and the complete binary comparison. Matching text can still have different stored representations. Decide which of those identities the application actually requires.

When adapting a validation, retain identical, case-different, trailing-space-different and missing pairs. Test the complete representation required by the contract. A display-only comparison can overlook the very difference that matters.

When the bytes matter, compare the bytes.

A text match is not proof of identical bytes, it is a comparison that ignores trailing spaces.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.