Banging me head against the wall with this one.
I have table containing postcodes and street names and I have another table where Houses are listed for sale ( where the Street name is missing) and I am tryin to get the Street name for each post code.
The problem is that table 1 stores the postcode without the space and table 2 which I am trying to update stores the post code with the space.
So in table 1 the postcode is stored as "l249pb" and table 2 it is stored as "l24 9pb".
Now if the post codes where both stored in exactly the same format i.e without the space I would expect this query to work:
UPDATE Table1
INNER JOIN Table2 ON ( Table1.PostCode = Table2.PostCode )
SET Table1.StreetName = Table2.StreetName
I have tried this but it wont work :
UPDATE Table1
INNER JOIN Table2 ON ( Table1.PostCode = REPLACE(Table2.PostCode,' ',''))
SET Table1.StreetName = Table2.StreetName
can anyone tell me how to check for a match ignoring spaces ( like a trim but removing every space )
Many thanks for any help you can offer.