By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3610123 fiddles created (46901 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;
