SQLite wildcards in Join

Question

In a previous question of mine it appears as though my problem is SQLite dependent. I have simplified the problem as follows:

Say I have two tables: table1 as follows

Name     | col1
------------------------
 Bob     | B1
Mary Jane| M1

and table2 as follows:

Name     | col2
------------------------------
 Bob     | B2
Mary     | M2

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

NameA     | NameB |col1| col2
-------------------------------------
 Bob      | Bob   | B1 | B2
Mary Jane | Mary  | M1 | M2

Using

SELECT tablea.Name as NameA, tableb.Name as NameB, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
ON tablea.Name LIKE tableb.Name

I get as output

      NameA NameB col1 col2
1       Bob   Bob  B1   B2
2 Mary Jane  <NA>  M1  <NA>

which is what I would expect. Now if I use wildcards as follows:

SELECT tablea.Name as NameA, tableb.Name as NameB, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
ON tablea.Name LIKE '%'+tableb.Name+'%'

I get:

      NameA NameB col1 col2
1       Bob  <NA>  B1  <NA>
2 Mary Jane  <NA>  M1  <NA>

It doesn't even match Bob's entries?!

Am I missing something? Is this a problem with SQLite?

For completeness, I'm using the sqldf function with R which uses SQLite.

score 2 · Accepted Answer · answered Dec 28 '14 at 10:51

2

Use the || operator to concatenate strings, not +

answered Dec 28 '14 at 10:51

wvdz

16,251
4
53
90

This indeed seems to work! Many thanks. I assume that this is the standard concatenate operator in SQLite. – Geoff Dec 28 '14 at 10:54
Actually, it is standard concatenate operator in all SQL dialects I know. It is a little bit inconsistent with most programming languages though... – wvdz Dec 28 '14 at 10:58
Oh. My previous question though yielded answers with `+` throughout. – Geoff Dec 28 '14 at 10:59

score 0 · Answer 2 · answered Dec 28 '14 at 13:23

For your sample data, you don't need the LEFT JOIN:

SELECT tablea.Name AS NameA, tableb.Name AS NameB, tablea.col1, tableb.col2
FROM table1 as tablea
INNER JOIN table2 as tableb
ON tablea.Name LIKE '%'||tableb.Name||'%';

SELECT tablea.Name AS NameA, tableb.Name AS NameB, tablea.col1, tableb.col2
FROM table1 as tablea
INNER JOIN table2 as tableb
ON INSTR(tablea.Name, tableb.Name) > 0;

Second version for a potential performance gain.

SQL Fiddle

SQLite wildcards in Join

2 Answers2