SQL Server, records with leading spaces or trailing spaces

When you have an application that is writing leading or trailing spaces in the database fields they can be hard to detect when view the results, especially the trailing spaces.  We have a 3rd party financial application where we installed a service pack.  The application started adding trailing spaces to the records.  The application has a customer table and that table stores the customer_id as a varchar value like "AB567". Well when we created invoices for that customer everything in the front-end worked fine but our custom reports started failing because the invoice had the customer_id of "AB567     ".

To get an idea of how bad the problem was we needed to identify the records.  I found that the "LEN" function in sql server will trim spaces out of the character count.  Seems odd but that's how it works.

To get around this you can use the DATALENGTH function instead.  So to identify the records we used the following sql to compare the datalength of the customer id on the invoice to the datalength of the trimmed customer_id.

select inv_no from invoice i
where datalength(i.customer_id) != datalength(ltrim(rtrim(i.customer_id)