How can I pass R variable into sqldf?

Question

I have some query like this:

sqldf("select TenScore from data where State_P = 'AndhraPradesh'")

But I have "AndhraPradesh" in a variable stateValue. How can I use this variable in a select query in R to get the same result as above.

Please show me the syntax.

Answer 1

You can use sprintf:

sqldf(sprintf("select TenScore from data where State_P = '%s'", stateValue))

Answer 2

See Example 5 on the sqldf GitHub page.

---

Example 5. Insert Variables

Here is an example of inserting evaluated variables into a query using gsubfn quasi-perl-style string interpolation. gsubfn is used by sqldf so it is already loaded. Note that we must use the fn$ prefix to invoke the interpolation functionality:

minSL <- 7
limit <- 3
species <- "virginica"
fn$sqldf("select * from iris where \"Sepal.Length\" > $minSL and species = '$species' 
  limit $limit")

##   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
## 1          7.1         3.0          5.9         2.1 virginica
## 2          7.6         3.0          6.6         2.1 virginica
## 3          7.3         2.9          6.3         1.8 virginica

Answer 3

You can also use fn$sqldf :

fn$sqldf("select TenScore from data where State_P = '$stateValue'")