R-vector to string of strings

Question

I am new to 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 variable length vector. 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 the quotes in brackets and get

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

which does not start in sqlQuery. Is there a way to use the paste command to 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' );" 

Answer 2

You can surround ' on " to make them truly part of the string:

 vector = c("'a'","'b'", .....) 

Example:

 > vec = c("'a'", "'b'", "'c'") > paste(vec, collapse = ', ') [1] "'a', 'b', 'c'" 

Answer 3

Put your vector in quotation marks before embedding it in your query.

 vector <- paste0("'", vector, "'", collapse=", ") query <- paste("select ....", vector, <etc>) 

shQuote does this for you, but this is an abuse of its purpose. This meant quoting strings for the OS shell, and there is no guarantee that its default selection will be what your database expects. For example, on Windows, it wraps everything in double quotes, which cmd.exe expects, but can break the query string.