asked on

Remove Suffix From Last Names

A little background information first. I have two tables, say Table1 and Table2. Both of these tables have a column called 'LastName'. I am trying to see if there is a match for the lastname in Table1 in Table2. I am cursoring through each record in Table1 and if there is a match in Table 2, I run a query on that record in Table2.

The problem is that some of the records in Table1 do not have a match because there is a suffix on the end of the last name. I have provided a few examples of the suffixes below:

Table1:
LastName
---------------------------
Brown Jr.
Smith Sr.
Edwards II
Johnson IV

Table2:
LastName
---------------------------
Brown
Smith
Edwards
Johnson

Is there a way that I can filter out the suffixes using SQL? If it were another language such as C# or PHP I know I could put the strings to filter out into an array and pull them out.

Does anyone have any ideas? I have attached a sample of my code below. Thanks for any help.

DECLARE @LastName VARCHAR(255)
DECLARE @FirstName VARCHAR(255)
DECLARE @DOB DATETIME
DECLARE @Company VARCHAR(255)
DECLARE @Company1 VARCHAR(255)

SET @Company1 = 'CompanyName' /* Company to fix records for */

DECLARE fixit CURSOR FOR

/* Select records from Table1 */
SELECT [Last Name], [First Name] ,[DOB], Company1
FROM [Table1]
WHERE Company1 = @Company1
ORDER BY [Last Name], [First Name], [DOB]

OPEN fixit

FETCH NEXT FROM fixit INTO @LastName,@FirstName,@DOB,@Company

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @LastName AS LastName,@FirstName AS FirstName,@DOB AS DOB,@Company AS Company

/* Check if person exists in Table2 */
IF NOT EXISTS
	(
		SELECT 1 FROM [Table2]
		WHERE Company1 = @Company1
		AND LastName LIKE '%' + @LastName + '%'
		AND FirstName = @FirstName
		AND [Date Of Birth] = @DOB
	)
	BEGIN
		/* Record does not Exist. Remove identifiying information from Table1. */		
		UPDATE [Table1]
		SET CompanyID = 1
		WHERE Company1 = @Company1
		AND [Last Name] = @LastName
		AND [First Name] = @FirstName
		AND [DOB] = @DOB
	END

FETCH NEXT FROM fixit INTO @LastName,@FirstName,@DOB,@Company

END

CLOSE fixit
deallocate fixit

Open in new window

SOLUTION

JimyLee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

You have queried the first table (Table 1) and again updating the same table (Table 1). I don't understand what you are doing in your query. You can also avoid cursor to update every record. To get rid of suffix, you can try as JimyLee suggested.

MattStewart01

ASKER

That works great to remove the suffix. I also have names without a suffix in the lastname. Would it possible to check to in the query if the suffix exisits? And if it doesn't exist, do not alter the string.

Bhavesh Shah

SELECT [Last Name], [First Name] ,[DOB], Company1
FROM [Table1]
WHERE Company1 = @Company1
and charindex([last name],' ') > 0
ORDER BY [Last Name], [First Name], [DOB]

Bhavesh Shah

Without cursor, if u wanted to UPDATE Data then

UPDATE [Table1]
 SET CompanyID = CASE WHEN B.Company1 IS NULL THEN 1 ELSE A.CompanyID END

FROM Table1 A LEFT OUTRER JOIN Table2 B
ON A.Company1 = B.Company1
AND  LEFT(A.LAST_NAME) = B.LAST_NAME
AND  A.FIRST_NAME = B.FIRST_NAME
AND A.DOB = B.DOB

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Just realised that table1 is the one with the suffix, so swap the LIKE parts

UPDATE Table1
SET CompanyID = 1
WHERE Company1 = @Company1
AND Not Exists (
      SELECT 1 FROM [Table2]
      WHERE [Table2].Company1 = Table1.Company1
      AND ([Table2].LastName = Table1.LastName OR Table1.LastName LIKE Table2.LastName + ' %')
      AND [Table2].FirstName = Table1.FirstName
      AND [Table2].[Date Of Birth] = Table1.DOB)