SQL Server’s LEN() Function

  • [アーティクル]

There is a peculiarity around the LEN() function in SQL Server that not many people have realized or paid attention to. It’s the treatment of trailing spaces. The documentation says:

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

(The highlighting is mine.)

 

Indeed

          SELECT LEN('abc'), LEN('abc ');

returns: 3 3, not 3 12 as most people would expect.

 

I tried to deduce the rationale behind this behavior even though that doesn’t change the behavior at all: It must have appeared as a remedy for fixed-length character strings – char and nchar. Since those are always padded with blank spaces to fill up the entire slot, LEN() over values of those types would always return the size of the type, regardless of the value. That would be pretty useless. Unfortunately this behavior has been extended over to variable-length character strings where all characters, including trailing blanks, have a meaning.

 

Is SQL Server alone in this? No, there are other database servers with the same behavior. You can try the above query on your server, and see how it treats trailing spaces. What can we do?

a. If strings like 'abc' and 'abc '  are equivalent in your application, this behavior is perfect for you. You may eventually consider trimming the trailing spaces explicitly using RTIM() to be more explicit.

b. If you do want to distinguish between those two strings, you should consider appending a special, non-blank, character at the end that you can strip off when you load strings from the database. You should also subtract the number of such special characters from what LEN() returns. If you have strings with corner-case lengths – 4,000 for nvarchar and 8,000 for varchar, you should consider casting values to nvarchar(max) or varchar(max) respectively before appending anything. Otherwise, the characters that exceed the type size limit will be silently lost.

Comments

  • Anonymous
    December 19, 2007
    I recently blogged about canonical functions in Entity Framework and SQL Server’s LEN() function . The

  • Anonymous
    December 19, 2007
    I recently blogged about canonical functions in Entity Framework and SQL Server’s LEN() function . The

  • Anonymous
    September 26, 2010
    In stead of LEN(), if DATLENGTH() is used, it will return number of characters including trailing blanks....