SQL SERVER 2008 LEN() AND SPACES: The Debacle

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):

A crazy wall of horizontally and vertically stacked old books with a rustic blue wood door in the center. The words "Support MindFuel Blog on Patreon are shamelessly plastered over this fine image

PRINT LEN(‘ ‘)

Now run it (F5).

0

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!

 

2 thoughts on “SQL SERVER 2008 LEN() AND SPACES: The Debacle”

Leave a Comment

Your email address will not be published. Required fields are marked *