Post reply

Can you match text rows using LIKE in a join?

  • January 29, 2004 at 8:46 am

    #107811

    Dear Forum,

    I'm trying to match 2 tables where one table has just the surname, while the other table has the surname within it, but also a lot of other junk such as titles first names etc.

    I want to match the two tables just using these variables.

    I've tried to match the two tables using a join of the form

    table1.name LIKE '%[table2.name]%'

    But it's not working. I guess because you can't put variables in a matching statement?

    Can this kind of LIKE matching be done in a join?

    Thanks, Bill

  • January 29, 2004 at 9:01 am

    #492186

    Instead of

    table1.name LIKE '%[table2.name]%'

    Try

    table1.name LIKE '%' + [table2.name] + '%'

    Or optionally

    table1.name LIKE '%' + RTRIM([table2.name]) + '%'



    Once you understand the BITs, all the pieces come together

  • January 29, 2004 at 9:12 am

    #492187

    Thanks again Thomas!

    This is what worked:

    table1.name LIKE '%' + table2.name + '%'

    Cheers, Bill

  • January 29, 2004 at 4:18 pm

    #492269

    How about using:

    WHERE CHARINDEX(table1.name,table2.name) > 0

    Will that work for you?


    Richard D. Cushing
    Sr. Consultant
    SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

  • January 30, 2004 at 3:58 am

    #492296

    Thanks Richard,

    I see that CHARINDEX looks for a string occuring inside another.

    You've put it in the WHERE statement but I can't work out how I would then join my tables?

    Bill

  • January 30, 2004 at 6:43 am

    #492309

    SELECT table1.name,table2.name

    FROM table1

    INNER JOIN table2

    ON CHARINDEX(table2.name,table1.name) > 0

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 6 posts - 1 through 5 (of 5 total)

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