4

How can I use the LIKE clause with an inner join using sqldf in R?

The code:

Name <- c("Jack","Jill","Romeo")
Name <- as.data.frame(Name)
FullName <- c("School Jack H", "School Juliet G", "College Jill M", "College Romeo F")
Marks <- c("100","82","54","0")
FullBio <- cbind(FullName, Marks)
FullBio <-as.data.frame(FullBio)

And then when I run:

sqldf("select a.*, b.* from Name a join FullBio b on a.Name like '%'+b.[FullName]+'%'")

returns 0 rows.

Why? What are my other alternatives please. I apologise for making you create so many variables to run my code.

CuriousBeing
  • 1,592
  • 14
  • 34

1 Answers1

4

The string concatenation operator is || in SQLite:

sqldf("select * from Name join FullBio on FullName like '%' || Name || '%'")

giving:

   Name        FullName Marks
1  Jack   School Jack H   100
2  Jill  College Jill M    54
3 Romeo College Romeo F     0

Any of these would also work:

sqldf("select * from Name join FullBio on instr(FullName, Name)")

sqldf("select * from Name join FullBio on like('%' || Name || '%', FullName)")
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • It did not work for me. Im on a Macbook and Im hitting the key next to "]" with shift. I tried this before and it didnt work. Edit: It worked. But it does not work when I use qualifiers like a and b. Do you know why? – CuriousBeing Dec 17 '15 at 11:40
  • 1
    It works for me. Copy the code from the answer and paste it into R just to be sure you haven't made a typo. Also make sure sqldf is working by trying a very simple query: such as `sqldf("select * from BOD")` – G. Grothendieck Dec 17 '15 at 11:45