How to use character string in R inside SQL WHERE IN statement

Question

I would like to use a string of character IDs in the WHERE clause of SQL query. My id object looks like this

ids
 [1] "0000000000000000010000001" "0000000000000000010000002" "0000000000000000010000003"
 [4] "0000000000000000010000004" "0000000000000000010000005" "0000000000000000010000006"
 [7] "0000000000000000010000007" "0000000000000000010000008" "0000000000000000010000009"
[10] "0000000000000000010000010"

I would like to insert that object into this SQL query:

student <- sqlQuery(con,
  "select
    FSAS.a_id,
    FSAS.grade,
    FSAS.score,
    FSAS.placement,
    FSAS.start,
    FSAS.completion

    FROM db.Fact AS FSAS
      WHERE FSAS.a_id IN ids"

)

I think the entire SQL query has to be a string but I can't figure out how to get it right.

akrun · Accepted Answer · 2019-11-25 20:58:38Z

4

We can use paste to create the expression

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s)", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

edited Nov 25, 2019 at 20:58

answered Nov 25, 2019 at 20:43

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

r2evans Over a year ago

Why do you load sqldf?

akrun Over a year ago

@r2evans While reading I somehow had a mental image of sqldf instead of sqlQuery. Thanks for correcting that

Kate N · Accepted Answer · 2019-11-25 20:44:35Z

0

This should work:

qry = paste("select
    FSAS.a_id,
    FSAS.grade,
    FSAS.score,
    FSAS.placement,
    FSAS.start,
    FSAS.completion

    FROM db.Fact AS FSAS
      WHERE FSAS.a_id IN", ids, sep = "")

student <- sqlQuery(con,
     qry
)

answered Nov 25, 2019 at 20:44

Kate N

4534 silver badges15 bronze badges

2 Comments

r2evans Over a year ago

Nooooo! (xkcd.com/327) Please don't suggest this as a viable solution, it will break in horrible ways when ids is not perfectly compliant.

SmokeyShakers Over a year ago

qry will be a character vector, the length of ids. Essentially reproducing the query, over and over. And none of those queries will be valid anyway.

Collectives™ on Stack Overflow

How to use character string in R inside SQL WHERE IN statement

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related