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.

score 13 · Accepted Answer · answered Sep 25 '11 at 20:15

13

You can use sprintf:

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

answered Sep 25 '11 at 20:15

rcs

64,778
22
167
150

I would not recommend this. See the Example 5 on the sqldf github home page for the method normally used with sqldf. – G. Grothendieck Sep 15 '17 at 12:00

G. Grothendieck · Answer 2 · 2020-05-11T11:55:14.150

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

score 3 · Answer 3 · edited Nov 16 '13 at 23:40

3

You can also use fn$sqldf :

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

edited Nov 16 '13 at 23:40

CDub

12,860
4
50
66

answered Nov 16 '13 at 23:12

Mikah

31
2

How can I pass R variable into sqldf?

3 Answers3

Linked

Related