Post reply

MATCH NAME IN DIFFERENT TABLES WHERE NAME ORDER IS REVERSED

  • August 6, 2015 at 2:34 pm

    #398858

    CREATE TABLE #NAME1(FULLNAME VARCHAR (100))

    INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')

    INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')

    CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))

    INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')

    INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')

    My task is to find matches between these two tables on "name." Does anyone have a suggestion on where to begin?

  • August 6, 2015 at 3:10 pm

    #1817986

    It looks like name coding is not conformed even within the same table, but you can work around this by unionizing multiple selects, each with an alternate join expression to cover all known patterns.

    SELECT *

    FROM #NAME1

    JOIN #NAME2 on FIRSTNAME+' '+MI+'. '+LASTNAME = #NAME1.FULLNAME

    UNION ALL

    SELECT *

    FROM #NAME1

    JOIN #NAME2 on LASTNAME+' '+FIRSTNAME = #NAME1.FULLNAME

    UNION ALL

    SELECT *

    FROM #NAME1

    JOIN #NAME2 on LASTNAME+' '+REPLACE(FIRSTNAME,' ','. ') = #NAME1.FULLNAME;

    FULLNAME LASTNAME MI FIRSTNAME

    JOHN X. DOE DOE X JOHN

    FITZGERALD F. SCOTT FITZGERALD NULL F SCOTT

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply