SQL Server 2008: How to find trailing spaces

Question

How can I find all column values in a column which have trailing spaces? For leading spaces it would simply be

select col from table where substring(col,1,1) = ' '; 

Answer 1

You can find trailing spaces with LIKE:

SELECT col FROM tbl WHERE col LIKE '% ' 

Answer 2

SQL Server 2005:

select col from tbl where right(col, 1) = ' ' 

As a demo:

select      case when right('said Fred', 1) = ' ' then 1 else 0 end as NoTrail,     case when right('said Fred ', 1) = ' ' then 1 else 0 end as WithTrail 

returns

NoTrail WithTrail 0       1