SQL Datalength vs. Len Functions

The other day I had an SSAS cube process failure which stemmed from values padded with spaces in our database. To fix this issue I ended up having to use a new SQL function I hadn’t heard of called “DATALENGTH”. Most colleagues of mine use the traditional “LEN” function to return the length of a given string. This quick post is aimed at outlining the differences in the two functions and when you should be using them.

LEN – This function returns the number of characters, rather than the number of bytes, of the given string. LEN will exclude trailing blanks and spaces.

Example: select LEN(MYSTRING) from dbo.MYDB

DATALENGTH – This function returns the number of bytes used to represent any type of expression.

Example: select DATALENGTH(MYSTRING) from dbo.MYDB

So when do you use each function? Typically, the LEN function is used to return the length of a string expression when you don’t care about blanks or want to overlook them. Alternatively, DATALENGTH is used to find the number of bytes and should therefore be used when you’re worried about blanks and trailing spaces.

For more documentation you can visit Microsoft Developer’s Network:

LEN( )

DATALENGTH( )