Why does the command SELECT LEN('123' + ' '), which counts the number of characters in a field, return three when it should return eight? Three characters plus five blank spaces is eight characters in total. How can I count the blank spaces?
When in doubt about unexpected behavior, read SQL Server Books Online (BOL). The BOL entry for the LEN() function says that it ignores trailing blanks. Many people make the mistake of assuming the LEN() function will count trailing blanks because similar functions in other programming languages do. To get the answer you're looking for, use the DATALENGTH() function instead:
SELECT DATALENGTH('123' +
' ')