CREATE TABLE CustNamesOld(CustName varchar(255))
GO
CREATE TABLE CustNamesNew(FirstName varchar(30), LastName varchar(50),MiddleInit char(1),)
GO
INSERT INTO CustNamesOld(CustName) 
SELECT 'AAROON W BURR' Union All
SELECT 'BENJAMIN P FRANKLIN' Union All
SELECT 'CAPTAIN JAMES TIBERIUS KIRK' Union All
SELECT 'DOUGLAS FAIRBANKS JR.' Union All
SELECT 'ELLA R FITZGERALD' Union All
SELECT 'FRANKLIN DELANO ROOSEVELT' Union All
SELECT 'GORDIE HOWE' Union All
SELECT 'HAROLDS PUB AND GRILL' Union All
SELECT 'MS. IVANA TRUMP' Union All -- Hey I had too
SELECT 'JACKSON BROWNE' Union All
SELECT 'KATHY P IRELAND' Union All -- Ditto
SELECT 'LOUIS S ARMSTRONG' Union All
SELECT 'MIKE THE MAD DOG RUSSO' Union All
SELECT 'NATHAN HALE' Union All
SELECT 'MR. OSCAR P WILDE' Union All
SELECT 'PIERRE C TURGEON' Union All
SELECT 'QUIGLEYS TAVERN'

-- Build something to help filter

CREATE FUNCTION udf_WORDS
	(@str varchar(8000))
RETURNS int
AS
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@x <> 0)
		BEGIN
			SET @x = CHARINDEX(' ', @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @Words
END

-- First try and identify the problem cases, this will be your filter

SELECT *, dbo.udf_WORDS(CustName) FROM CustNamesOld
WHERE 
-- Business terms 
(CustName LIKE '%and %' OR CustName LIKE '%INC%' OR CustName LIKE '%TAVERN%' OR CustName LIKE '%GRILL%') --ect
-- Titles and Prefixes
OR (CustName LIKE '%MR %' OR CustName LIKE '%MS %' OR CustName LIKE '%MRS %' OR CustName LIKE '%CAPTAIN %') --ect
-- Non conforming names <> to 2 spaces
OR (dbo.udf_WORDS(CustName) NOT IN (2,3))

DROP TABLE CustNamesOld
GO
DROP TABLE CustNamesNew
GO
DROP FUNCTION udf_WORDS
GO