CODEWITHSUNDEEP
sqlrrodbc
waanders
waanders

Reputation: 9083

Dynamic "string" in R

Simple question, but cannot find the answer.

Instead of:

Df <- sqlQuery(ch, "SELECT * FROM tblTest WHERE Id=25")

I want a more dynamic piece of code. Something like:

Id <- 25
Df <- sqlQuery(ch, c("SELECT * FROM tblTest WHERE Id=", Id))

But this is not correct.

Upvotes: 5

Views: 4971

Answers (4)

Zaki
Zaki

Reputation: 141

Use glue_sql(). Example:

Id <- 25

Df <- sqlQuery(ch, glue_sql("SELECT * FROM tblTest WHERE Id= {Id}", .con = ch))

More info here.

Upvotes: 1

Justin
Justin

Reputation: 394

Just want to update this with a more modern answer using dplyr/tidyverse which uses string interpolation via the str_glue command:

str_glue("SELECT * FROM tblTest WHERE Id = {Id}")

You can put any expression you want in there. For example, if you wanted to select multiple Id's using a vector:

str_glue("SELECT * FROM tblTest WHERE Id IN ({str_c(id_vector, collapse = \",\"})")

Upvotes: 2

Bryan Prazen
Bryan Prazen

Reputation: 363

If you have multiple or reused arguments and a query that contains % you can use something like the following:

sprintf("SELECT * FROM test WHERE id = %1$s AND name = %2$s and type like ‘%%tall%%’”, id, name)

Upvotes: 1

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

We can use paste:

Df <- sqlQuery(ch, paste("SELECT * FROM tblTest WHERE Id =", Id))

c concatenates into a vector, paste is for string concatenation.

Or we can use sprintf:

sprintf("SELECT * FROM tblTest WHERE Id = %s", Id)

Upvotes: 8

Related Questions