Applies to SQL Server 2019, 2022 and 2025.

SQL Server trailing spaces are one of those things that sit quietly in a table for years until a report shows two “identical” customers, or an import into another system fails on a value that looks fine on screen. They’re hard to spot because SQL Server goes out of its way to ignore them in most of the places you’d normally look.

Here’s how I find them, why the obvious checks don’t work, and how to stop them coming back.

Why SQL Server trailing spaces are easy to miss

Three bits of standard behaviour work together to hide them:

• LEN returns the number of characters excluding trailing spaces. ‘Smith’ and ‘Smith ‘ both give 5.
• Equality comparisons follow the ANSI rule of padding the shorter string with spaces before comparing, so ‘Smith’ = ‘Smith ‘ is true. That applies to char, varchar, nchar and nvarchar alike, and to GROUP BY, DISTINCT and unique indexes too.
• Grid results in SSMS don’t show you the spaces at the end of a value.

What does see them is DATALENGTH, which returns the number of bytes actually stored, trailing spaces included. Remember it’s bytes, not characters, so an nvarchar value takes two bytes per character.

A quick demonstration

CREATE TABLE #Customer
(
    CustomerId   int IDENTITY(1,1) PRIMARY KEY,
    CustomerName varchar(50) NOT NULL
);

INSERT INTO #Customer (CustomerName)
VALUES ('Smith'), ('Smith '), ('Jones   '), ('Brown');

SELECT  CustomerId,
        '[' + CustomerName + ']'  AS ShownName,
        LEN(CustomerName)         AS CharCount,
        DATALENGTH(CustomerName)  AS ByteCount
FROM    #Customer;

-- Returns 2, not 1
SELECT COUNT(*) AS SmithCount
FROM   #Customer
WHERE  CustomerName = 'Smith';

Wrapping the value in square brackets is the simplest way I know to make the spaces visible. Rows 1 and 2 both have a CharCount of 5, but ByteCount is 5 and 6. The equality test happily matches both.

Finding the rows

Compare the stored length with the length after RTRIM, using DATALENGTH on both sides so the units match:

SELECT  CustomerId,
        '[' + CustomerName + ']' AS ShownName
FROM    #Customer
WHERE   DATALENGTH(CustomerName) > DATALENGTH(RTRIM(CustomerName));

That returns the Smith with one trailing space and the Jones with three. It works the same for nvarchar columns because both sides are measured in bytes. On a big table it will scan, as the function on the column stops any index seek, so run it out of hours or against a restored copy.

The common mistake

The checks most people try first return nothing, which looks like a clean bill of health:

-- Both of these find no rows, even though two rows have trailing spaces
SELECT CustomerId FROM #Customer WHERE LEN(CustomerName) <> LEN(RTRIM(CustomerName));
SELECT CustomerId FROM #Customer WHERE CustomerName <> RTRIM(CustomerName);

The first fails because LEN has already ignored the spaces, so both sides are always the same. The second fails because the comparison pads the trimmed value back out before comparing, so the two are always equal. Neither is a bug; it’s documented behaviour. The fix is to use something that counts what is stored, which is DATALENGTH.

A related trap is LIKE. Unlike =, trailing spaces in a LIKE pattern are significant, and the rules for the value being matched differ between Unicode and non-Unicode data. I don’t rely on LIKE to find trailing spaces; DATALENGTH gives the same answer whatever the data type.

Cleaning up and keeping it clean

Once you’ve reviewed the rows, trim them, then add a check constraint so new ones can’t get in:

UPDATE  #Customer
SET     CustomerName = RTRIM(CustomerName)
WHERE   DATALENGTH(CustomerName) > DATALENGTH(RTRIM(CustomerName));

ALTER TABLE #Customer
ADD CONSTRAINT CK_Customer_NoTrailingSpace
CHECK (DATALENGTH(CustomerName) = DATALENGTH(RTRIM(CustomerName)));

Before the UPDATE on a real table, check for duplicates. If you have a unique index on the column, SQL Server already treats ‘Smith’ and ‘Smith ‘ as the same key, so you won’t hit a clash there, but application code outside the database may have been treating them as different records.

Watch out for

• char and nchar columns are always padded to their full length. Every short value has “trailing spaces” by design, so this check is only meaningful on varchar and nvarchar.
• Other whitespace. RTRIM only removes ordinary spaces (CHAR(32)). Tabs, carriage returns, line feeds and non-breaking spaces (CHAR(160)) stay put and need REPLACE or TRIM with a characters list.
• ANSI_PADDING. Leave it ON. It’s the default, and setting it OFF is deprecated; it changes how trailing blanks are stored in new columns.
• Leading spaces are a separate problem: LEN and = both count them, so they’re easier to find but still worth checking for in the same pass.

If you’re putting together a set of data quality checks like this, my SQL Server DBA Handbook has the rest of the scripts I run regularly, and my T-SQL coding conventions post covers some related habits. Microsoft’s documentation for LEN and LIKE spells out the trailing space rules.

This topic came up in the SQLServerCentral Question of the Day: Finding Trailing Spaces.

Clive Roberts DBA