Passing a vector of characters into another string in R

Question

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.

score 2 · Answer 1 · answered Oct 20 '22 at 17:17

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')"

score 1 · Accepted Answer · answered Oct 21 '22 at 22:19

If you are going to generate a lot of SQL, I recommend that you look into glue_sql(), because it is especially designed for this use case and has a lot of nice features:

library(glue)

## set up example db:
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "chickwts", chickwts)

tbl <- "chickwts"
feed <- list("soybean", "sunflower")
feed <- glue_sql_collapse(single_quote(feed), sep = ", ")

glue_sql("
  SELECT * FROM {`tbl`}
  WHERE feed IN ({feed})
  ", .con = con)
#> <SQL> SELECT * FROM `chickwts`
#> WHERE feed IN ('soybean', 'sunflower')

^{Created on 2022-10-21 with reprex v2.0.2}

Passing a vector of characters into another string in R

2 Answers2