clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591703 fiddles created (45707 in the last week).

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
 hidden batch(es)


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);
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


SELECT * FROM MyTable;
Id FirstName LastName
1 Bridget Jones , III
2 Butch Jones
3 Key West
4 Bob Marly Junior
 hidden batch(es)