Post reply

Using Full-Text to "match" rows in two different tables

  • December 8, 2011 at 1:40 pm

    #247492

    I'm hoping someone out there can lend some assistance and possibly tell me if this is even possible. I'm looking to find a way to match and/or rank partial column matches in two tables. I'm thinking full-text might be my best bet?

    Table 1

    IDNameAddress

    1Bob Smith123 Main St.

    2John Williams111 Park Place

    3Robert Johnson789 Atlantic St.

    Table 2

    IDNameAddress

    1Bob Smith123 Main Apt. 55

    2John Williams234 Avenue B

    3Robert Johnson789 Atlantic St.

    Using the sample data above:

    - Robert Johnson (ID 3) in both tables are a perfect match.

    - John Williams (ID 2) are obviously two different people as their addresses are completely different.

    - Bob Smith (ID 1) in both tables are a possible match as they both live at "123 Main", but one is listing an apartment number and is omitting the street abbreviation. I'm hoping that I could use full-text to rank the likeliness of it being a match. I could possibly join both tables to each other using a LIKE, but what if "Main" is misspelled as "Mayne"? I guess I could use SOUNDEX at that point, but it seems like a never ending list of tests that I'm hoping utilizing full-text to alleviate.

    Any ideas would be welcome. I have a follow-up question regarding similar names (Robert vs. Bob, Will vs. William), but will wait until I have a solution for the above first.

    Thanks in advance.

  • December 8, 2011 at 3:10 pm

    #1418991

    Can you set up a sample table with a few rows of data? The link in my sig will show you how to do this.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • December 8, 2011 at 3:15 pm

    #1418995

    It's a big topic and will require some research, but look into 'fuzzy lookup' in SSIS.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 8, 2011 at 3:15 pm

    #1418996

    FTS is going to need the data to be on one table as well. you would have to have two searches then compare the rankings. That might work.

    I have always used FTS for more keyword search, but it doesnt mean it work for what you are trying to do.

    Is all this data in one col? just two tables.

Viewing 4 posts - 1 through 3 (of 3 total)

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