How to Compare Strings In Two Different VARCHAR Columns?
First I've used an split string function that I've borrowed (again) from this answer. And I've added an ID column (I suppose your table has some PK field to identify every record.)




CREATE TABLE MyTable
(
    Id int IDENTITY,
    FirstName varchar(500),
    LastName varchar(500)
)

INSERT INTO MyTable (FirstName, LastName) 
VALUES ('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West'),('Bob Marly', 'Junior');
GO






CREATE FUNCTION dbo.fnSplit(@Input Varchar(1000), @Splitter VarChar(10)) 
RETURNS TABLE AS
RETURN
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS Data 
    FROM (SELECT CAST ('&lt;M&gt;' + REPLACE(@Input, @Splitter, '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS Data) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a);
GO



Now, using a CROSS APPLY with your data:




    SELECT *
    FROM   MyTable t1 
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) &gt; 0;
GO



You can identify the records where LastName contains any word of FirstName:



Id | FirstName     | LastName   | Data 
-: | :------------ | :--------- | :----
 1 | Bridget Jones | Jones, III | Jones
 3 | Key West      | West       | West 



Using ID's returned from previous query you can update your table:




WITH found AS
(
    SELECT Id, FirstName, LastName, Data
    FROM   MyTable t1 
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) &gt; 0
)
UPDATE     T1
SET        T1.LastName = RTRIM(LTRIM(REPLACE(T1.LastName, Data, '')))
FROM       MyTable t1
INNER JOIN found t2
ON         t1.Id = t2.Id;
GO



2 rows affected



And this is the final result:




SELECT * FROM MyTable;
GO



Id | FirstName     | LastName
-: | :------------ | :-------
 1 | Bridget Jones | , III   
 2 | Butch         | Jones   
 3 | Key West      |         
 4 | Bob Marly     | Junior  



dbfiddle here