Joining tables based off LIKE

Transact-SQL
1

I have two tables that list out companies. There are 1000s of companies in database a but only about 150 in database b. Unfortunately, the companies are written a bit differently in each of the tables. For example, table a might have 'Walmart Inc' while table b might just have 'Walmart'. Is there a way to join these two tables off the first word in the company name for each?

2

That is why masterdata is so important. I would try to make sure the names and all the data is the same so you can join it based on a common key but to answer your question:

You can use the function SUBSTRING to select a certain part of the word, in this example the first to the place of the space, found by the function CHARINDEX. I use the function LTRIM and RTRIM to remove any unwanted spaces.

DROP TABLE IF EXISTS #a
DROP TABLE IF EXISTS #b

SELECT 'Walmart inc' AS Company INTO #a
SELECT 'Walmart' AS Company INTO #b

SELECT 
	a.Company,
	CHARINDEX(' ',a.Company) AS PositionOfTheSpace, 
	LTRIM(RTRIM(SUBSTRING(a.Company,1,CHARINDEX(' ',a.Company)))) As CompanyClean,
	b.Company
FROM #a a
	LEFT OUTER JOIN #b b ON (LTRIM(RTRIM(SUBSTRING(a.Company,1,CHARINDEX(a.Company,' '))))=b.Company)