Go ahead and fire up SQL Server (I have 2008 R2) and type the following into a query window (c’mon, you know you wanna):
PRINT LEN(‘ ‘)
Now run it (F5).
That’s right, no matter how many space you put in, the length is 0. This has apparently been around a while and a workaround is to use DATALENGTH, which does evaluate spaces…it does it in context of varchar vs nvarchar… so a space on an nvarchar is 2 bytes. That isn’t always the answer I want to see!
Now, various documents say LEN() returns the number of characters excluding trailing blanks, so maybe they feel they are off the hook. But to me, space is very much a character – it is 1 character…and SQL Server is even more the character for treating space the way it does!