Nov 12: 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)
« previous page   (Page 1 of 1, totaling 1 entries)   next page »