LEN function not including trailing spaces in SQL Server

user_115704 0
Viewed 99317

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?

sql-server
11 Answers

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 function - 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
user_1157040

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.

user_1059290

This is the best algorithm I've come up with which copes with the maximum length and variable byte count per character issues:

ISNULL(LEN(STUFF(@Input, 1, 1, '') + '.'), 0)

This is a variant of the LEN(@Input + '.') - 1 algorithm but by using STUFF to remove the first character we ensure that the modified string doesn't exceed maximum length and remove the need to subtract 1.

ISNULL(..., 0) is added to deal with the case where @Input = '' which causes STUFF to return NULL.

This does have the side effect that the result is also 0 when @Input is NULL which is inconsistent with LEN(NULL) which returns NULL, but this could be dealt with by logic outside this function if need be

Here are the results using LEN(@Input), LEN(@Input + '.') - 1, LEN(REPLACE(@Input, ' ', '.')) and the above STUFF variant, using a sample of @Input = CAST(' S' + SPACE(3998) AS NVARCHAR(4000)) over 1000 iterations

Algorithm DataLength ExpectedResult Result ms
LEN 8000 4000 2 14
+DOT-1 8000 4000 1 13
REPLACE 8000 4000 4000 514
STUFF+DOT 8000 4000 4000 0

In this case the STUFF algorithm is actually faster than LEN()!

I can only assume that internally SQL looks at the last character and if it is not a space then optimizes the calculation But that's a good result eh?

Don't use the REPLACE option unless you know your strings are small - it's hugely inefficient

user_10921560
Related