R vector into String of Strings

Question

I'm new to both R and mySQL and would like to run the following mysql command in R

query = "select x, y from table where z in ('a', 'b');"
sqlQuery(connection, query)

Suppose I have a very long vector of variable length. Is it possible to do

vector = c('a','b', .....)
query = "select x, y from table where z in **vector**;"

I tried

query = paste("select x, y from table where z in (", paste(vector, collapse =', '), ");")

but I lose quotes in the brackets and I get

query = "select x, y from table where z in (a, b);"

which does not run in sqlQuery. Is there a way to use the paste command so that I get a string of strings? Or is there a better way to do what I would like to accomplish?

Answer 1

You need to use shQuote

query <- paste("select x, y from table where z in (", paste(shQuote(vector, type = "sh"), 
         collapse = ', '), ");")
query
[1] "select x, y from table where z in ( 'a', 'b', 'c', 'd' );"