Q: Pattern matching on joins Say I have two tables, one called InternalCustomers which contains all my customers, and a table called ExternalCustomers which contains customer information from an external source.
In my table I have a Legal_Id for the customer which is an official id for the company, which also exists in the external table. However my table contains a mix of a lot of customers from various sources (and data quality is not perfect) and the Legal_Id is therefore not necessarily unique. 
Further my Customer_Name is not the legally registered name for the Company, but has just been typed in by an account manager, so it may read something different than the official name (e.g. may read Google Incorporated rather than Google Inc.)
My challenge is to join these two tables together with some sort of Pattern-matching, but I don't know if this is even possible?
InternalCustomers (17 mio different customers):
| Legal_Id | Customer_Name          |
|----------|------------------------|
| 1234     | Chr.Hansen Group       |
| 1235     | Apple Inc              |
| 1236     | A.P. Moller Maersk A/S |

ExternalCustomers (no control):
| Legal_Id | Customer_Name          |
|----------|------------------------|
| 1234     | Christian Hansen AS    |
| 1235     | Nokia                  |
| 1236     | Mærsk                  |

Expected result when doing a left join:
| Legal_Id | Customer_Name          | Legal_Id (External) | Customer_Name (External) |
|----------|------------------------|---------------------|--------------------------|
| 1234     | Chr.Hansen Group       | 1234                | Christian Hansen AS      |
| 1235     | Apple Inc              | NULL                | NULL                     |
| 1236     | A.P. Moller Maersk A/S | 1236                | Mærsk                    |

So to summarize: I want to do a left join between the two tables on Legal_Id and a pattern-match on Customer_Name if possible. 
Apple <> Nokia, therefore this join should not be completed - but A.P. Moller Maersk A/S = Mærsk and this join should be completed.
Customer names are not always easily identifiable so that I could just match a partial name - e.g. A.P Moller Maersk A/S would be fairly easy for a human to match up to Mærsk, but a machine will say that Maersk <> Mærsk.
Is this in any way achievable?

A: I wouldn't call this a robust solution (and certainly not elegant), but it does handle the provided examples.
note: SplitCSVToTable8K is renamed version of the popular DelimitedSplit8K function.
IF OBJECT_ID('tempdb..#TableA', 'U') IS NOT NULL 
DROP TABLE #TableA;
GO 
CREATE TABLE #TableA (
    Legal_Id INT NOT NULL,
    Customer_Name VARCHAR(30) NOT NULL 
    );
INSERT #TableA (Legal_Id, Customer_Name) VALUES
    (1234, N'Chr.Hansen Group'),
    (1235, N'Apple Inc'),
    (1236, N'A.P. Moller Maersk A/S');
GO 
IF OBJECT_ID('tempdb..#TableB', 'U') IS NOT NULL 
DROP TABLE #TableB;
GO 
CREATE TABLE #TableB (
    Legal_Id INT NOT NULL,
    Customer_Name VARCHAR(30) NOT NULL 
    );
INSERT #TableB (Legal_Id, Customer_Name) VALUES
    (1234, N'Christian Hansen AS'),
    (1235, N'Nokia'),
    (1236, N'Mærsk');
GO

SELECT 
    ta.Legal_Id,
    ta.Customer_Name,
    [Legal_Id (Ext)] = tb.Legal_Id,
    [Customer_Name (Ext)] = tb.Customer_Name,
    sca.Item,
    scb.Item
FROM 
    #TableA ta
    JOIN #TableB tb
        ON ta.Legal_Id = tb.Legal_Id
    CROSS APPLY dbo.SplitCSVToTable8K(REPLACE(ta.Customer_Name, '.', ' '), ' ') sca
    CROSS APPLY dbo.SplitCSVToTable8K(REPLACE(tb.Customer_Name, '.', ' '), ' ') scb
WHERE 
    '%' + CAST(sca.Item AS NVARCHAR(4000)) + '%' LIKE '%' + CAST(scb.Item AS NVARCHAR(4000)) + '%';


