I'm trying to match approximately similar company names between columns. I've used
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table2.company_name
LIKE CONCAT('%', table1.company_name, '%')
OR table1.company_name
LIKE CONCAT('%', table2.company_name, '%')
As well as other methods such as REGEXP and even the Levenshtein algorithm for MYSQL with no luck. The method above seems to match ~60% of the rows but not the rest. I couldn't find a pattern with the ones that wouldn't match, since many of the ones that wouldn't match look much like the ones that do. Is there another way to match even more names on top of this method? Such as a way to use LIKE CONCAT for the first few characters of each row in the columns?
EDIT: In response to some comments, I've also augmented the above with
OR (table1.company_name != table2.company_name) AND (SOUNDEX(table1.company_name) = SOUNDEX(table2.company_name))
Which gives me the exact same result as the above code, so it seems that SOUNDEX doesn't help either. Additionally, I thought FULLTEXT was inapplicable when trying to find partial matches between columns. If not, could someone illustrate to me how to do this?
