2006.01.05 09:18 PM


Hard to believe I never ran into this before, but look at the T-SQL LEN function's documentation very carefully:

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Who knew? Probably everyone. That explains why this:

declare @s varchar(25)
set @s = '*a*b '
select len(@s), right(@s, len(@s)-1)

Returns a 4 and '*b ', instead of the desired 5 and 'a*b '.

In this case the data was incorrect and shouldn't have been loaded with trailing spaces in the first place (easily fixed). But it took a while to figure out why the data, when accumulated into delimited strings and subsequently stripped of leading delimiters, sometimes returned values devoid of the first legitimate character. I never suspected LEN wasn't returning the full length, trailing spaces and all.

Live and learn.


DATALENGTH works to get you the full count, FYI.

Eric | 2006.07.06 05:09 AM

Eric, thanks. I actually had a note in my blog book to come back and talk about DATALENGTH as it relates to this post, but never got around to it. Beyond consideration of trailing blanks, there are interesting subtle differences between LEN's counting of characters and DATALENGTH's counting of bytes that I wanted to explore. Maybe someday. Thanks for pointing it out.

ewbi.develops | 2006.07.06 06:30 AM

> Maybe someday. Thanks for pointing it out.

It's been more than a year. Maybe in 2008 or 2009?

Everyone | 2007.07.08 04:46 AM

The keyword was "maybe", not "someday". But thanks for the reminder, Everyone.

ewbi.develops | 2007.07.08 08:48 AM

-- ====================================================================================
-- Author: Alexander Lizano (2008-Sep-07)
-- Description: Return the ACTUAL length of a string.
-- This function is necessary because the SQL Server funtion Len() doesn’t
-- count spaces at the end of the string. (Yes, incredible)
-- Example : Length('1234 ') returns 5 as Len('1234 ') returns 4
-- ====================================================================================
CREATE FUNCTION Length(@str nvarchar(MAX))
DECLARE @lenWithoutTrailingBlanks int, @trailingBlanks int, @charCount int

SET @lenWithoutTrailingBlanks = Len(@str)
SET @trailingBlanks = 0

-- Count blanks at the end
SET @charCount = @lenWithoutTrailingBlanks + 1

WHILE '*' + SubString(@str, @charCount, 1) + '*' = '* *' -- A simple comparation with ' ' woudn't work
SET @trailingBlanks = @trailingBlanks + 1
SET @charCount = @charCount + 1

RETURN @lenWithoutTrailingBlanks + @trailingBlanks

Alexander Lizano | 2008.09.07 03:55 PM

Thanks for sharing that, Alexander. I'd forgotten about this post (and issue), but I know folks still wind up here looking for answers. Hopefully someone will benefit from your sharing this with us. Thanks!

ewbi.develops | 2008.09.08 08:05 AM


TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8349d92f853ef

Listed below are links to weblogs that reference SQL Server T-SQL LEN: