TSQL 2008 Using LTrim(RTrim and still have spaces in the data

Question

I have data I cleaning up in an old data table prior to moving it to a new one. One of the fields has spaces in the column, right & left. I wrote the following code to address this and still have leading spaces?? The bulk of the data is clean when using this code but for some reason there are spaces prior to RT addresses... Has anyone else had this type of issue?

,CASE   
WHEN PropStreetAddr IS NOT NULL
 THEN  (CONVERT(VARCHAR(28),PropStreetAddr))  
WHEN PropStreetAddr is NOT NULL Then  (Select LTrim(RTrim(PropStreetAddr)) As PropStreetAddr)
     ELSE NULL END  as 'PROPERTY_STREET_ADDRESS'

Sample output data:

1234 20th St 
  RT 1 BOX 2  
560 King St  
610 Nowland Rd  
  RT 1  
1085 YouAreHere Ln  
  RT 24 Box 12  

Answer 1

I've had the same problem - wrapping the string in a CAST(x as varbinary(64)) shows the hex and from this I see A000 - which I believe is non-breaking space.

To remove, try this (for UNICODE);

LTRIM(RTRIM(REPLACE(my-column, NCHAR(0x00A0), '')))