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:
So... just cast your expression to NVARCHAR(MAX) or VARCHAR(MAX) where appropriate and you'll get the right length
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
So... just cast your expression to NVARCHAR(MAX) or VARCHAR(MAX) where appropriate and you'll get the right length
