How to Compare Strings In Two Different VARCHAR Columns?

I am in need of a way to compare the content of two varchar columns, named fistname and lastname correspondingly, and if the content exists in firstname, then remove it from lastname. Ideally, I would like to do this w/o an update query, but if that is the only way to achieve it then I can go that route.

Here is sample DDL and DML:

Declare @BadData Table
(
    firstname varchar(500)
    ,lastname varchar(500)
)

Insert Into @BadData (firstname, lastname) Values
('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West')
,('Bob Marly', 'Junior')

Select * From @BadData

Which gives the resultset of:

firstname       lastname
-------------   ----------
Bridget Jones   Jones, III
Butch           Jones
Key West        West
Bob Marly       Junior

My desired resultset is:

firstname       lastname
-------------   --------
Bridget Jones   , III
Butch   Jones
Key West    
Bob Marly       Junior

I want to remove the text (that is, the content) from the lastname column if it exists in the firstname column.

How can this be done via a UDF or case statement in SQL Server 2008 R2?

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 ('' + REPLACE(@Input, @Splitter, '') + '' 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) > 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) > 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

StackExchange Database Administrators Q#176530, answer score: 7