Length Issue in SQL Server while adding spaces in varchar

Question

Problem:

I have three variables @str1, @str2, @str3. So when I add spaces at start of the variable, It gives me right length, but when I add spaces at last it gives me wrong result.

Solution:

To avoid this behavior I could use trim the variable before checking the length of that variable.

Question:

why it happens anb What is the benefit of doing this(trimming spaces from right side, not left)?

 declare @str1 varchar(30), @str2 varchar(30), @str3 varchar(30)
    SET @str1 = ' kratika,vikas,kritika'
    SET @str2 = 'kratika,vikas,kritika '
    SET @str3 = 'kratika,vikas,kritika'

PRINT LEN(@str1)
PRINT LEN(@str2)
PRINT LEN(@str3)

Output

    22
    21
    21

Please let me know if question is not clear.

This is a well know feature of SQL Server. http://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server — Phil, Mar 18 '13 at 11:49
Check the documentation http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx — praveen, Mar 18 '13 at 11:49

score 2 · Accepted Answer · answered Mar 18 '13 at 11:48

That's what it's documented to do:

Returns the number of characters of the specified string expression, excluding trailing blanks.

, and,

To return the number of bytes used to represent an expression, use the DATALENGTH function.

There are a number of features in SQL which all treat trailing blank characters as somewhat special. For instance, the strings 'abc ' and 'abc' are considered to be equal because effectively, spaces are added to the shorter string before the comparison occurs.

If you want to determine the length of a string, including trailing blanks, then the obvious thing is to concatenate a single non-blank to the string passed to LEN() and then subtract one from the result.

Length Issue in SQL Server while adding spaces in varchar

1 Answers1