CODEWITHSUNDEEP
sqlrdatabase
user13467695
user13467695

Reputation:

How to paste an R vector to sql query?

I have a vector with values:

id = c("a1", "s44", "qq11", "f5")

I want to write the sql query and paste those values in it:

dbGetQuery(con, "SELECT * from my.table WHERE col1 in id")

As you see I want those values be after "in" in query. How to paste it there?

Upvotes: 1

Views: 1427

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269526

Use sprintf like this. It works with an arbitrary number of elements. (If id contained numbers instead of character strings then the inner sprintf could be replaced with id as we would not need the single quotes surrounding each element.)

Note that dot is an SQL operator so my.table needs to be escaped (or choose a name not containing dot).

# inputs
id <-  c("a1", "s44", "qq11", "f5")
my.table <- data.frame(col1 = c("s44", "t44", "s44", "u44"), col2 = 1:4)

sql.template <- "SELECT * from [my.table] WHERE col1 in (%s)"
sql <- sprintf(sql.template, toString(sprintf("'%s'", id)))
sql
## [1] "SELECT * from [my.table] WHERE col1 in ('a1', 's44', 'qq11', 'f5')"

Test with the data frame defined above

library(RSQLite)

con <- dbConnect(SQLite())
dbWriteTable(con, "my.table", my.table)

dbGetQuery(con, sql)
##   col1 col2
## 1  s44    1
## 2  s44    3

dbDisconnect(con)

Upvotes: 1

Till
Till

Reputation: 6628

id <-  c("a1", "s44", "qq11", "f5")

First you want to concatenate the elements of id with commas, so that it can be inserted into your SQL statement.

sql_list_string <- paste(id, collapse = "', '")
sql_list_string
#> [1] "a1', 's44', 'qq11', 'f5"

Then, paste() it into your SQL statement.

query <-
  paste0("SELECT * from my.table WHERE col1 in ('", sql_list_string, "')")
query
#> [1] "SELECT * from my.table WHERE col1 in ('a1', 's44', 'qq11', 'f5')"

You can now use query in your call to dbGetQuery() like this: dbGetQuery(con, query)

Upvotes: 2

tadoop
tadoop

Reputation: 21

You need to use dbGetQuery params argument.

    id = c("a1", "s44", "qq11", "f5")
    dbGetQuery(con, "SELECT * from my.table WHERE col1 in (?,?,?,?)",
    params=id)

You will need one ? for each element of the vector. dbGetQuery

Upvotes: 2

Related Questions