Jane Dallaway

Jane Dallaway

Often found in front of a computer, loom or sewing machine.

Software developer by trade. Weaver and photographer by hobby. Dog owner by design. This blog has elements of them all.

Maintainer of 30yearsagotoday.com and brightonbloggers.com


Email: jane @ dallaway.com
Twitter: @janedallaway
Flickr: janed
Instagram: janed

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 Disqus