Need a query to looking for cases of period difference in Legal names

Question

Post reply

Need a query to looking for cases of period difference in Legal names

kkbear

SSC-Addicted

Points: 457
More actions
June 9, 2015 at 8:56 am

#298807

How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd.
All Season Equipment Ltd
West End Housing, Inc.
West End Housing, Inc
Wellings, Norman L.
Wellings, Norman L
North Texas Boats, LLC
North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.)
Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C
S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc.
Burke Equipment Company
Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C.
Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc.
J&D Lawn & Tractor Sales, Inc"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this seems to find matches:

;WITH MySampleData([ID],[CompanyName])

AS

(

SELECT '1','All Season Equipment Ltd.' UNION ALL

SELECT '2','All Season Equipment Ltd' UNION ALL

SELECT '3','West End Housing, Inc.' UNION ALL

SELECT '4','West End Housing, Inc' UNION ALL

SELECT '5','Wellings, Norman L.' UNION ALL

SELECT '6','Wellings, Norman L' UNION ALL

SELECT '7','North Texas Boats, LLC' UNION ALL

SELECT '8','North Texas Boats, L.L.C.' UNION ALL

SELECT '9','Oktibbeha County Cooperative (A.A.L.)' UNION ALL

SELECT '10','Oktibbeha County Cooperative (AAL)' UNION ALL

SELECT '11','S & R Turf & Irrigation Equipment, L.L.C' UNION ALL

SELECT '12','S & R Turf & Irrigation Equipment, L.L.C.' UNION ALL

SELECT '13','Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc.' UNION ALL

SELECT '14','Burke Equipment Company' UNION ALL

SELECT '15','Burke Equipment-Seaford, Inc.' UNION ALL

SELECT '16','Pleasant Valley Outdoor Power, L.L.C.' UNION ALL

SELECT '17','Pleasant Valley Outdoor Power, LLC' UNION ALL

SELECT '18','J & D Lawn and Tractor Sales, Inc.' UNION ALL

SELECT '19','J&D Lawn & Tractor Sales, Inc'

)

SELECT * FROM MySampleData T1

--INNER JOIN MySampleData T2 ON REPLACE(T1.[CompanyName],'.','') = REPLACE(T2.[CompanyName],'.','')

INNER JOIN MySampleData T2 ON CHARINDEX(REPLACE(T1.[CompanyName],'.',''),REPLACE(T2.[CompanyName],'.','')) >0

WHERE T1.ID > T2.ID --don't let the row find itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgmunson SSC Guru Points: 110640 More actions · Answer 2

It also appears you have other non "period related" similarities, so you may need to strip out all white space, periods, and possibly even commas, in order to have a "shot" at finding all the "sufficiently similar" names. No method is likely to be 100% foolproof. Lowell has provided a pretty good start. You may need to extend that. Look closely at records 18 and 19, and you'll see that spaces are missing (or excess), and that might well mean a similarity.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)