LEN function not including trailing spaces in SQL Server

Question

I have the following test table in SQL Server 2005:

CREATE TABLE [dbo].[TestTable]
(
 [ID] [int] NOT NULL,
 [TestField] [varchar](100) NOT NULL
) 

Populated with:

INSERT INTO TestTable (ID, TestField) VALUES (1, 'A value');   -- Len = 7
INSERT INTO TestTable (ID, TestField) VALUES (2, 'Another value      '); -- Len = 13 + 6 spaces

When I try to find the length of TestField with the SQL Server LEN() function it does not count the trailing spaces - e.g.:

-- Note: Also results the grid view of TestField do not show trailing spaces (SQL Server 2005).
SELECT 
 ID, 
 TestField, 
 LEN(TestField) As LenOfTestField, -- Does not include trailing spaces
FROM 
 TestTable

How do I include the trailing spaces in the length result?

Answer 1

This is clearly documented by Microsoft in MSDN at http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx, which states LEN "returns the number of characters of the specified string expression, excluding trailing blanks". It is, however, an easy detail on to miss if you're not wary.

You need to instead use the DATALENGTH fuction - see http://msdn.microsoft.com/en-us/library/ms173486(SQL.90).aspx - which "returns the number of bytes used to represent any expression".

Example:

SELECT 
    ID, 
    TestField, 
    LEN(TestField) As LenOfTestField,           -- Does not include trailing spaces
    DATALENGTH(TestField) As DataLengthOfTestField      -- Shows the true length of data, including trailing spaces.
FROM 
    TestTable

Answer 2

You need also to ensure that your data is actually saved with the trailing blanks. When ANSI PADDING is OFF (non-default):

Trailing blanks in character values inserted into a varchar column are trimmed.