I encountered a TSQL issue this morning that I knew looked familiar. When doing SELECT LEN(@strName) it was returning less characters than I expected. So, when doing SELECT LEN(' ') I got a result of 0 instead of 1, when I did SELECT LEN('Jane ') I got 4 rather than 5. I'd forgotten about the fact that it trims off white space before returning data. Thanks to Adam Machanic I refound DATALENGTH which does what I'd expect.LEN defined in SQL Server Books online as Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.DATALENGTH defined as Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.
comments powered by