Passing a vector of characters into another string in R

October 20, 2022

I would like to know how to pass a vector of text into a string within R.

I have a list of emails stored as a character vector:

all.emails <-
list(
c('email_1@emailaddress_1.com',
'email_2@emailaddress_2.com',
'email_3@emailaddress_3.com',
'email_r@emailaddress_n.com'
 )
)

Also within R, I have some SQL code stored as a string that I will pass to our database via a database connection in R. To do this, I created a string that is the query written in SQL but I want to pass the emails above into the string below so I can query the database only for those emails.

The SQL query will look something like this:

sql <-
"
1> SELECT column_1, column_2,..., column_n
2> FROM name.of.table
3> WHERE toaddress = '[this is where to pass the email list above into]'.
"

It is line 3 where I need to pass my email list into.

Any help will be appreciated.

>Solution :

You can create the sql statement as follows:

sql = paste0(
  "SELECT column_1, column_2,..., column_n ",
  "FROM name.of.table ",
  "WHERE toaddress IN ('",
  paste0(unlist(all.emails),collapse="','"),
  "')"
)

Output:

"SELECT column_1, column_2,..., column_n FROM name.of.table WHERE toaddress IN ('email_1@emailaddress_1.com','email_2@emailaddress_2.com','email_3@emailaddress_3.com','email_r@emailaddress_n.com')"