SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
4 rows affected

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 ('<M>' + REPLACE(@Input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a);
    SELECT *
    FROM   MyTable t1 
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) > 0;


Id
FirstName
LastName
Data


1
Bridget Jones
Jones, III
Jones

3
Key West
West
West


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

SELECT * FROM MyTable;


Id
FirstName
LastName


1
Bridget Jones
, III

2
Butch
Jones

3
Key West


4
Bob Marly
Junior