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?
Best Answer
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.)
Now, using a CROSS APPLY with your data:
You can identify the records where
LastName
contains any word ofFirstName
:Using ID's returned from previous query you can update your table:
And this is the final result:
dbfiddle here