Here's another option. It considers that the address can be identified by the first 8 characters. You can adjust that value.

SELECT *, LEFT( a.address, CHARINDEX( LEFT( a.address, 8), a.address, 2) - 1)

--UPDATE a SET address = New_Address

FROM address_fix a;