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 strange blue cyber background with black lines going vertical and horizontal. The image of a woman takes up a third of the frame and she is starting at a the small image of a meme where someone is wearing a rainbow afro. "Support MindFuel Blog on Patreon" is meticulously crafted into a graphical overlay which obfuscates part of the underlying artistry. So sad.


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!


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 *