SQL Join with wildcards

Question

Say I have two tables: table1 as follows

Name     |Surname| col1
------------------------
 Bob     |Smith  | BS1
Mary Jane|Jones  | MJ1

and table2 as follows:

Name     |Surname       | col2
------------------------------
 Bob     |Keller Smith  | BS2
Mary     |Jones         | MJ2

What I would like is to JOIN these together to obtain:

Name     |Surname       | col1| col2
-------------------------------------
 Bob     |Keller Smith  | BS1 | BS2
Mary     |Jones         | MJ1 | MJ2

I tried:

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
ON '%'+tablea.Name+'%' LIKE '%'+tableb.Name+'%' AND '%'+tablea.Surame+'%' LIKE '%'+tableb.Surame+'%'

But these seemed to join everything.

How can I join together columns with wildcards correctly?

(i) The `'%'` to the left of the `LIKE`s are most likely superfluous (at best) - but definitely not evaluated as placeholders by the `LIKE` operator. (ii) Is the blank in front of "Bob" intentional? — Abecee, Dec 28 '14 at 10:56

score 2 · Accepted Answer · answered Dec 28 '14 at 08:45

2

Try this:

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
    ON (tablea.Name LIKE '%' + tableb.Name + '%' OR tableb.Name LIKE '%' + tablea.Name + '%')
    AND (tablea.Surname LIKE '%' + tableb.Surname + '%' OR tableb.Surname LIKE '%' + tablea.Surname + '%')

answered Dec 28 '14 at 08:45

dario

5,149
12
28
32

This didn't work: I got ` Name Surname col1 col2 1 BS1 2 MJ1 ` – Geoff Dec 28 '14 at 09:42
I just tried it, and it returns exactly the output you expected. – dario Dec 28 '14 at 09:54
Oh, could this be an MySQL or SQLite issue? – Geoff Dec 28 '14 at 09:57
I'm using the `sqldf` function in `R`. – Geoff Dec 28 '14 at 10:03
I don't know them. I can only say that this work on SQL Server and should work on MySQL. – dario Dec 28 '14 at 10:08
I solved the problem. Your code is correct, but in SQLite (which I only mentioned too late - apologies) the concatenate operator is `||` not `+`. – Geoff Dec 28 '14 at 10:57

score 1 · Answer 2 · answered Dec 28 '14 at 08:51

1

I think you want an INNER JOIN.

Also note that the percent signs are only recognized on the right hand side of the LIKE operator. Joining Name with Surname also doesn't seem right

SELECT 
  a.Name, b.Surname, a.col1, b.col2
FROM 
  table1 AS a
  INNER JOIN table2 AS b ON 
    a.Name LIKE '%' + b.Name + '%'
    OR b.Name LIKE '%' + a.Name + '%'

answered Dec 28 '14 at 08:51

Tomalak

332,285
67
532
628

@Tomalek You are correct about the Name and Surname. I'll edit my question accordingly. – Geoff Dec 28 '14 at 09:28
This didn't work: I got `[1] Name Surname col1 col2 <0 rows> (or 0-length row.names)` – Geoff Dec 28 '14 at 09:41

score 0 · Answer 3 · answered Dec 28 '14 at 11:34

For your sample data either one of the following works:

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
INNER JOIN table2 as tableb
ON tablea.Name LIKE '%'+tableb.Name+'%'
  AND tableb.Surname LIKE '%'+tablea.Surname+'%';

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
INNER JOIN table2 as tableb
ON CHARINDEX(tableb.Name, tablea.Name) > 0
  AND CHARINDEX(tablea.Surname, tableb.Surname) > 0;

in SQL Server Fiddle and (with the concatenation adjusted, and INSTR taking over for CHARINDEX) in SQL Lite Fiddle.
The latter version might perform better, especially when indices could be used, which (at least in some database systems) are ignored with the LIKE operator.

SQL Join with wildcards

3 Answers3

Linked