Display all records with leading or trailing spaces

Question

I am looking for a SQL query (Using SQL 2008) to search various fields that I specify (or even all fields in particular tables) for leading or trailing spaces. I would like the output to be two columns with the following data:

ID Number, Data (that has the space), Field Name

Thanks for the help as usual!

Answer 1

You can use DATALENGTH

SELECT ID, Data, FieldName
FROM table
WHERE DATALENGTH(RTRIM(LTRIM(Data))) <> DATALENGTH(Data)

Answer 2

Try:

select `ID Number`, `Data (that has the space)`, `Field Name` from tbl WHERE data like ' %' or data like '% '