CODEWITHSUNDEEP
rstringpastesqldf
Chirayu Chamoli
Chirayu Chamoli

Reputation: 2076

Add single quote to start and end of the vector of a string which would be fed into sqldf

I have vector of string like this:

vec1=letters[1:5] 
df=data.frame(a=1:10, b=letters[1:10])

I want to subset this data frame by feeding in the string in SQL statement.

vec1=letters[1:5]
vec3=paste("'", paste(vec1, collapse = "','"), "'", sep = '')
p=paste("select * from df where b in (", vec3, ")")
sqldf(p)

This works well but is there any elegant way to do this task. I tried with squotes too but it doesn't gives me the desired single quote.

vec3=paste(vec1, collapse = "','")
vec3=sQuote(vec3)

Upvotes: 1

Views: 1208

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269346

The sqldf package loads the gsubfn package providing fn for this purpose. fn supports backtick and dollar substitutions for code and single variables respectively. For example, try any of these. See ?fn and the examples on the sqldf github page.

fn$sqldf("select * from df where b in ( `toString(shQuote(vec1, 'sh'))` ) ")

p <- fn$identity("select * from df where b in ( `toString(shQuote(vec1, 'sh'))` ) ")
sqldf(p)

s <- toString(shQuote(vec1, 'sh'))
p <- fn$identity("select * from df where b in ( $s ) ")
sqldf(p)

Upvotes: 2

drmariod
drmariod

Reputation: 11762

You can use sprintf for this.

sprintf("'%s'", paste(vec1, collapse = "','"))

create a function will make it more readable...

addQuotes <- function(x) sprintf("'%s'", paste(x, collapse = "','"))
addQuotes(vec1)

Upvotes: 3

Related Questions