1

Suppose I have the following dataset

d <- data.frame(1:31, 31:1)
names(d) <- c("1st", "2nd")

And I want to select the columns where "3" occurs in the column named "1st" (i.e. column 3, 13 and 31 should be selected, but not column 1, 9, and 29).

sqldf("select * from d where 1st LIKE '%3%'")

gives me the error:

Error: unrecognized token: "1st"

How can I selectively subset part of a data frame based on a partial match?

Desired output:

1st    2nd
3       29
13      19
31      1

Any help is much appreciated

zx8754
  • 52,746
  • 12
  • 114
  • 209
Icewaffle
  • 443
  • 2
  • 13

1 Answers1

3

We have illegal column names, wrap it in square brackets:

sqldf("select * from d where [1st] LIKE '%3%'")
#   1st 2nd
# 1   3  29
# 2  13  19
# 3  23   9
# 4  30   2
# 5  31   1

Regarding illegal:

I haven't found a reference for it, but table names that are valid without using brackets around them should be any alphanumeric combination that doesn't start with a digit

zx8754
  • 52,746
  • 12
  • 114
  • 209