select *
from mytable
where bus_name like '%costco%'

SELECT  SOUNDEX('Costco'),
        SOUNDEX('COSTCO'),
        SOUNDEX('Costco Whls'),
        SOUNDEX('Costco Wholesale'),
        SOUNDEX('Costco Whls llc');

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