SQL Server : distinct with trailing whitespace

Question

How can I get this example to display ('space') and ('space ')?

create table #example
( 
    myString varchar(50)
)

insert into #example values ('space'), ('space ')

select distinct * 
from #example

I am aware SQL comparison operators consider these two strings as equivalent, but I do not for this case.

https://support.microsoft.com/en-us/kb/316626

SQL WHERE clause matching values with trailing spaces

Follow-up to answer:

Note: the DATALENGTH(...) function can also produce a join sensitive to trailing whitespace:

select *
from table1
left join table2 on table1.id1 = table2.id1 
                 and datalength(table1.id1) = datalength(table2.id1)

Answer 1

Try this:

SELECT A.myString
FROM (
    SELECT DISTINCT myString, DATALENGTH(myString) [DataLength]
    FROM #example
    ) A

With this data:

insert into #example values ('space'),
('space '),
('space2'),
('space2');

Results:

myString
--------
space
space 
space2

Answer 2

you can achieve that by removing the distinct keyword in your select stmt ...

select myString from #example

OR

select * from #example

But I suppose there is more to this question than just this ... ?

EDIT:

select  myString  
--, rtrim(myString)  as newstring -- uncomment for troubleshooting
from #example
WHERE rtrim(myString)  not like '% %'