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.)

    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');
CREATE FUNCTION dbo.fnSplit(@Input Varchar(1000), @Splitter VarChar(10)) 
    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);

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;

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
SET        T1.LastName = RTRIM(LTRIM(REPLACE(T1.LastName, Data, '')))
FROM       MyTable t1
INNER JOIN found t2
ON         t1.Id = t2.Id;
2 rows affected

And this is the final result:

Id | FirstName     | LastName
-: | :------------ | :-------
 1 | Bridget Jones | , III   
 2 | Butch         | Jones   
 3 | Key West      |         
 4 | Bob Marly     | Junior  

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.