wow, thats going to be tough;
the only thing i could think of was a combination of opc.three's example, and joining it against a list of common suffixes to find potential duplicates, but that of course is going an ongoing thing as you dig deeper into the data.
something like this is what i thought might be a starting point:
With MySampleData(CompanyName)
AS
(
SELECT 'Costco' UNION ALL
SELECT 'Costco LLC' UNION ALL
SELECT 'Costco Whls' UNION ALL
SELECT 'Home Interiors Malaga' UNION ALL
SELECT 'Home Plumbing' UNION ALL
SELECT 'Home Property Management' UNION ALL
SELECT 'Home Realty' UNION ALL
SELECT 'Home Svc'
),
CommonSuffixes (val)
AS
(
SELECT ' Inc' UNION ALL
SELECT ' LLC' UNION ALL
SELECT ' Company' UNION ALL
SELECT ' Co'
)
SELECT
ROW_NUMBER() OVER (PARTITION BY SOUNDEX(CompanyName) ORDER BY CompanyName) AS RW,
SOUNDEX(CompanyName) AS SoundX,
*
FROM MySampleData
LEFT OUTER JOIN CommonSuffixes
ON CHARINDEX(CommonSuffixes.val,MySampleData.CompanyName) > 0
--WHERE CommonSuffixes.val IS NOT NULL --(turns the LEFT OUTER into an inner join, i know)
ORDER BY CompanyName,RW
Lowell