Using SQLDF to select specific values from a column

Question

SQLDF newbie here.

I have a data frame which has about 15,000 rows and 1 column. The data looks like:

cars
autocar
carsinfo
whatisthat
donnadrive
car
telephone
...

I wanted to use the package sqldf to loop through the column and pick all values which contain "car" anywhere in their value. However, the following code generates an error.

> sqldf("SELECT Keyword FROM dat WHERE Keyword="car")
Error: unexpected symbol in "sqldf("SELECT Keyword FROM dat WHERE Keyword="car"

There is no unexpected symbol, so I'm not sure whats wrong.

so first, I want to know all the values which contain 'car'. then I want to know only those values which contain just 'car' by itself.

Can anyone help.

EDIT:

allright, there was an unexpected symbol, but it only gives me just car and not every row which contains 'car'.

> sqldf("SELECT Keyword FROM dat WHERE Keyword='car'")
  Keyword
1     car

You have at least one set of closing parentheses missing in your statement... — Andrie
– Andrie, Commented Jun 2, 2011 at 14:57
you need to escape the quotes sqldf("SELECT Keyword FROM dat WHERE Keyword=\"car\""). — deinst
– deinst, Commented Jun 2, 2011 at 14:59

Andrie · Accepted Answer · 2011-06-02 15:07:30Z

7

Using = will only return exact matches.

You should probably use the like operator combined with the wildcards % or _. The % wildcard will match multiple characters, while _ matches a single character.

Something like the following will find all instances of car, e.g. "cars", "motorcar", etc:

sqldf("SELECT Keyword FROM dat WHERE Keyword like '%car%'")

And the following will match "car" or "cars":

sqldf("SELECT Keyword FROM dat WHERE Keyword like 'car_'")

edited Jun 2, 2011 at 15:07

answered Jun 2, 2011 at 15:00

Andrie

180k52 gold badges456 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshua Ulrich · Accepted Answer · 2011-06-02 15:04:02Z

3

This has nothing to do with sqldf; your SQL statement is the problem. You need:

dat <- data.frame(Keyword=c("cars","autocar","carsinfo",
  "whatisthat","donnadrive","car","telephone"))
sqldf("SELECT Keyword FROM dat WHERE Keyword like '%car%'")
#    Keyword
# 1     cars
# 2  autocar
# 3 carsinfo
# 4      car

answered Jun 2, 2011 at 15:04

Joshua Ulrich

177k33 gold badges357 silver badges429 bronze badges

1 Comment

Joshua Ulrich Over a year ago

No problem. A handy-dandy SQL cheat sheet (courtesy of Google) would probably save you some time / headaches.

Chase · Accepted Answer · 2011-06-04 02:10:51Z

2

You can also use regular expressions to do this sort of filtering. grepl returns a logical vector (TRUE / FALSE) stating whether or not there was a match or not. You can get very sophisticated to match specific items, but a basic query will work in this case:

#Using @Joshua's dat data.frame
subset(dat, grepl("car", Keyword, ignore.case = TRUE))

   Keyword
1     cars
2  autocar
3 carsinfo
6      car

answered Jun 4, 2011 at 2:10

Chase

69.4k18 gold badges147 silver badges164 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:30:31Z

0

Very similar to the solution provided by @Chase. Because we do not use subset we do not need a logical vector and can use both grep or grepl:

df <- data.frame(keyword = c("cars", "autocar", "carsinfo", "whatisthat", "donnadrive", "car", "telephone"))
df[grep("car", df$keyword), , drop = FALSE] # or
df[grepl("car", df$keyword), , drop = FALSE]

   keyword
1     cars
2  autocar
3 carsinfo
6      car

I took the idea from Selecting rows where a column has a string like 'hsa..' (partial string match)

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Jun 24, 2015 at 13:09

mpalanco

13.7k3 gold badges66 silver badges71 bronze badges

Collectives™ on Stack Overflow

Using SQLDF to select specific values from a column

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related