When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.
I came across this cool query from Peter which shows how to convert a string to varbinary and find out the Binary, ASCII and Char of each character in the string. Here’s the query:
DECLARE @MyAddress varchar(35)
SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343'
DECLARE @BIN AS VARBINARY(100)
SET @BIN = convert(varbinary(100),@MyAddress)
SELECT SUBSTRING(@BIN, Number, 1) AS Binary,
ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII,
CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character
WHERE Type = 'p'
AND Number BETWEEN 1 AND DATALENGTH(@BIN)