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?
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)