Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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 > > <pre> 4 rows affected > </pre> <!-- --> > 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); > GO > > <pre> > ✓ > </pre> <!-- --> > SELECT * > FROM MyTable t1 > CROSS APPLY fnSplit(t1.FirstName, ' ') t2 > WHERE CHARINDEX(t2.Data, t1.LastName) > 0; > GO > > <pre> > Id | FirstName | LastName | Data > -: | :------------ | :--------- | :---- > 1 | Bridget Jones | Jones, III | Jones > 3 | Key West | West | West > </pre> <!-- --> > 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; > GO > > <pre> 2 rows affected > </pre> <!-- --> > SELECT * FROM MyTable; > GO > > <pre> > Id | FirstName | LastName > -: | :------------ | :------- > 1 | Bridget Jones | , III > 2 | Butch | Jones > 3 | Key West | > 4 | Bob Marly | Junior > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=cb87928d4cb9c367161d6ae373aba526)*
back to fiddle