Create a comma separated list from a column

Question

I have a table in R and I want to get the entries of a column in a dataframe to read out in a form of 'a,b,c,d,e' instead of [1] a b c d, as I want to pass it into a sql query for a WHERE DUMMY IN ('a,b,c,d,e')

Is there an easy way to do this? I'm guessing I make it a giant string and add a ',' at the end of every ' ' but I was wondering if it maybe a way to simply snag the column in a manner that R automatically reads it in this format.

Answer 1

The letters vector is in-built in R, and paste0 allows you to "collapse" vectors with specified separation characters, in this case a comma:

> paste0(letters[1:5], collapse=",")
[1] "a,b,c,d,e"

It wasn't clear what form these letters had in a dataframe, but if they were simply a column named, say letts, you could do this:

paste0( dfrm$letts, collapse=",")

Answer 2

I assume that what you really want is the string "'a', 'b', 'c', 'd'". We can use shQuote to quote the elements and toString to turn them into a single comma separated string. Finally insert it into the SQL statement using sprintf:

lets <- c("a", "b", "c", "d")

s <- toString(shQuote(lets))
s
## [1] "'a', 'b', 'c', 'd'"

sql <- sprintf("select * from mytab where mycol in (%s)", s)
sql
## [1] "select * from mytab where mycol in ('a', 'b', 'c', 'd')"

You can also use fn$ from the gsubfn package to perform the insertion. sqldf automatically loads gsubfn so this works using s from above:

library(sqldf)
fn$sqldf("select 'b' in ($s) as isPresent")
##   isPresent
## 1         1