Vfrdtyky

Table 1

SchoolId_1   Name

Table 2

SchoolId_2   Name

SchooldId_1 and SchooldId_2 have different values. Name have similar but not the same values.

In Table 1, I got name column value 'University of Maine -- Farmington'. In Table 2, I have 'University of Maine at Farmington'. I want to join these but sql skips them because they are not exactly the same.

Table1

University of Strathclyde

University of Maine Fort Kent

Bryn Athyn College of the New Church

Adirondack Community College

Allen Community College

Nova Scotia Community College

Unity College

Allen College

Table2

University of Strathclyde -- Glasgow

University of Maine at Fort Kent

Bryn Athyn College

Unity College

Allen College

I want to join them using the name column with values stated above and the results will be like

SchoolId_1    Name    SchoolId_2

1 University of Strathclyde 1101



2 University of Maine Fort Kent 1102



3 Bryn Athyn College of the New Church 1103



4 Adirondack Community College NULL



5 Allen Community College NULL



6 Nova Scotia Community College NULL



7 Unity College 1104



8 Allen College 1105

I tried LIKE, CHARINDEX but they require Name columns to have exactly the same values.

Current code (but only returns exactly the same name values):

SELECT

    dsa.SchoolId_1, dsa.Name, ds.SchoolId_2, ds.sch_address, ds.city, ds.sch_state 

INTO 

    match_sch 

FROM 

    dbo.sa_schools dsa 

LEFT JOIN 

    dbo.school ds ON dsa.Name = ds.Name;

Fantastic question! I have this problem constantly thanks to user-generated data on freeform text fields.

My solution was to build a UDF to use python to fix this. My UDF uses python and specifically the fuzzywuzzy library. I pass in two strings and it returns a numeric score which can be used for filtering.

eg

SELECT

    t1.schoolid_1

    ,t2.schoolid_2

    ,t1.name    as name_1

    ,t2.name    as name_2

FROM

    table_1 t1

LEFT JOIN

    table_2 t2

    ON

        my_udf(t1.name, t2.name) > 70

Although it looks like you're running on SQL Server, so python might not be available. In that case you could try to hack together something similar in a SQL-based UDF.

eg

SELECT

    1.0 * a.matches / a.rows as match_score

FROM

    (SELECT

        count(1) as rows

        ,count(CASE WHEN t1 is not NULL and t2 is not NULL THEN 1 END) as matches

    FROM

        (SELECT value FROM string_split(t1.name, '1')

        ) t1

    FULL OUTER JOIN

        (SELECT value FROM string_split(t2.name, '1')

        ) t2

        ON

            t1.value = t2.value

    ) a