Quirks of LEN

Arogl Darthu's Blog

Wednesday, November 12. 2008

Quirks of LEN

As we all know, the LEN function in T-SQL returns the character length and at that ignores the trailing blanks... So, how to return the length including these trailing blanks? I experimented a little and found that all you have to do is CAST to the appropriate MAXed variant of your datatype. So just try the following:

DECLARE @l_nContentMax NVARCHAR(MAX)
,       @l_nContent    NVARCHAR(20)

SELECT @l_nContentMax = '1234567890     ' -- character length = 15
,      @l_nContent    = '1234567890     ' -- character length = 15

SELECT LEN(N'1234567890     ')                        AS Plain
,      LEN(CAST(N'1234567890     ' AS NVARCHAR(MAX))) AS MaxCast
,      LEN(@l_nContent)                               AS PlainVariable
,      LEN(@l_nContentMax)                            AS MaxVariable
 
This results in:
T-Sql Results

So... just cast your expression to NVARCHAR(MAX) or VARCHAR(MAX) where appropriate and you'll get the right length ;-)
Posted by Twan Jacobs Comments: (0) Trackbacks: (0)

Comments
Display comments as (Linear | Threaded)

No comments


Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
You can use [geshi lang=lang_name [,ln={y|n}]][/lang] tags to embed source code snippets
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA