Below works! If I understood correctly 🙂

create table #test (

master_idint,

Titlevarchar(20),

Forenamevarchar(50),

Surnamevarchar(50),

Address1varchar(200),

Address2varchar(200),

Address3varchar(200),

Address4varchar(200),

Townvarchar(100),

Countyvarchar(100),

Postcodevarchar(20),

Hierarchyint

)

insert into #test

select 75984,'Mr','O','SQLady','6 Vivien Avenue Midsomer Norto','Midsomer Norton',NULL, NULL, 'RADSTOCK',NULL, 'BA6 2VG',2 union all

select 76144,'Mrs','N','Replacemento','56 Vivien Avenue Midsomer Nort','Midsomer Norton',NULL, NULL, 'RADSTOCK',NULL, 'BA6 2VG',2

select * from #test

DECLARE @index INT

DECLARE @Add1 VARCHAR(MAX)

DECLARE @Add2 VARCHAR(MAX)

DECLARE Addres CURSOR FOR SELECT address1, address2 FROM #test

OPEN addres

FETCH NEXT FROM addres INTO @add1, @add2

WHILE @@FETCH_STATUS = 0

BEGIN

SET @index = 1

WHILE @index <= LEN(@add2)

BEGIN

IF @Add2 = RIGHT(@add1, LEN(@Add2))

BEGIN

UPDATE #test

SET address1 = REPLACE(address1, @Add2, '')

WHERE address1 = @add1

END

SET @add2 = LEFT(@add2, LEN(@Add2)-1)

SET @index = @index +1

END

FETCH NEXT FROM addres INTO @add1, @add2

END

CLOSE addres

DEALLOCATE addres

SELECT * FROM #test

DROP TABLE #test