How to remove all whitespace from string?

This piece of code helped figure out exactly what kind of whitespace was present in the original query that had the join issue:

select distinct
fieldname,
space             = iif(charindex(char(32), fieldname) > 0, 1, 0),
horizontal_tab    = iif(charindex(char(9), fieldname) > 0, 1, 0),
vertical_tab      = iif(charindex(char(11), fieldname) > 0, 1, 0),
backspace         = iif(charindex(char(8), fieldname) > 0, 1, 0),
carriage_return   = iif(charindex(char(13), fieldname) > 0, 1, 0),
newline           = iif(charindex(char(10), fieldname) > 0, 1, 0),
formfeed          = iif(charindex(char(12), fieldname) > 0, 1, 0),
nonbreakingspace  = iif(charindex(char(255), fieldname) > 0, 1, 0)
from tablename;

It turned out there were carriage returns and new line feeds in the data of one of the tables. So using @scsimon's solution this problem was resolved by changing the join to this:

on REPLACE(REPLACE(a.fieldname, CHAR(10), ''), CHAR(13), '') = b.fieldname

Do you mean this? It removes a whitespace (as in created with a spacebar) character

Replace(str,' ', '')

I would suggest that you remove spaces and tabs (4 spaces):

SELECT REPLACE(REPLACE(str,' ', ''), char(9), '')