DATALENGTH vs. LEN in T-SQL (or detecting trailing spaces gotchas)

A client reported an issue with a SQL Server Reporting Services (SSRS) report that had recently been upgraded from 2008R2 to 2012.  The report was splitting a particular area into 2 pages (breaking) where it used to print all of that area on the same page, which is how the client still wanted it to print.

After looking at layout issues, I finally realized that the actual issue was that a group definition was evaluating some string values as different when they appeared to be the same.

The report was designed to look like this:

Group Header for value AA

Details for value AA

Group Footer for value AA

Group Header for value AB

Details for value AB

Group Footer for value AB

When the report ran – I got two sections (complete with header and footer) for value “AA”. Some of the details showed up in one section and some in the other.  When I ran the query – it looked like all the rows had the same value (“AA”).  I suspected trailing spaces or some other non-visible character, so I ran a select distinct … query and only got 1 occurrence of “AA”. I added a “where Field = ‘AA'” clause and got all of the rows. I added a LEN(Field) column and all of them showed up as “2”.

DATALENGTH to the rescue.  If you read this – you can see that SQL Server basically ignores trailing spaces when doing a string comparison. If you look at the documention for the LEN function, it says “… excluding trailing blanks”.

Turns out that LEN returns the number of characters and DATALENGTH returns the number of bytes. Once I added a DATALENGTH(field) column – I could see that some returned 2 and some returned 10.  My query was UNIONing several data sources together and some of the “AA” values were apparently defined in a CHAR(10) field.

I added some RTrim statements to the part of the query that was showing DATALENGTH of 10 and the report now ran as expected.

I’m not sure if it’s SQL or SSRS, but something has changed regarding this between 2008R2 and 2012, because this report ran for years the old way.