SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #testStringCompare
(
  id int identity(1,1) primary key,
  address1 varchar(100) not null,
  address2 varchar(100) not null
);
insert into #testStringCompare (address1, address2) values
('xyz xxx def','abc # Def')
,('69 Cherokee Cir Unit 204','69 CHEROKEE CIR # 204')
,('123 456 xyz','  123  456   xyz')
;
3 rows affected

select t.*
from #testStringCompare t
cross apply 
(
  select
   count(nullif(s1.value,' ')) as diffcnt1
  ,count(nullif(s2.value,' ')) as diffcnt2
  --,string_agg(nullif(s1.value,' '),'|') as diff1,
  --,string_agg(nullif(s2.value,' '),'|') as diff2
  from string_split(t.address1,' ') s1
  full join string_split(t.address2,' ') s2
    on lower(s1.value) = lower(s2.value)
  where s1.value is null
     or s2.value is null
) ca
where t.address1 != t.address2
  and (ca.diffcnt1>1 or ca.diffcnt2>1)


id
address1
address2


1
xyz xxx def
abc # Def


Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT *
FROM #testStringCompare
WHERE (LOWER(address1) != LOWER(address2)) 
  AND DIFFERENCE(LOWER(address1), LOWER(address2)) < 4


id
address1
address2


1
xyz xxx def
abc # Def