CODEWITHSUNDEEP
sqlrsqldf
Ernest Presley
Ernest Presley

Reputation: 171

r column values in sql where statement

I have a dataset and I am trying to pass the contents of a specific column into the SQL where statement.

For example, assuming iris is my dataset

       data(iris)
       head(iris)

       Sepal.Length Sepal.Width Petal.Length Petal.Width Species
       5.1         3.5          1.4         0.2  setosa
       4.9         3.0          1.4         0.2  setosa
       4.7         3.2          1.3         0.2  setosa
       4.6         3.1          1.5         0.2  setosa
       5.0         3.6          1.4         0.2  setosa
       5.4         3.9          1.7         0.4  setosa

I want to pass the contents of column Species { setosa, setosa, setosa.....setosa} to my sql query where statement

sqlQuery(abcd, paste("Select * from TestTableName1 
                       where WHERE DESCRIPTION 
                          IN (values of Species column from r dataframe)");

Need help here

Upvotes: 2

Views: 2263

Answers (2)

Gregor Thomas
Gregor Thomas

Reputation: 145775

Your question is really about string manipulation (it's incidental that your string will eventually be passed to sqldf), and the answer is that you paste it together, or use sprintf if you're feeling fancy:

vals = paste(paste0('"', levels(iris$Species), '"'), collapse = ", ")
vals
## [1] "\"setosa\", \"versicolor\", \"virginica\""

vals.paren = paste0("(", vals, ")")

qry = paste("select * from table where description in ", vals.paren)
qry
## [1] "select * from table where description in  (setosa, versicolor, virginica)"

# sprintf makes the bounding parentheses cleaner

qry = sprintf("select * from table where description in (%s)", vals)
qry
## [1] "select * from table where description in (setosa, versicolor, virginica)"

Upvotes: 5

G. Grothendieck
G. Grothendieck

Reputation: 269586

By prefacing any function call with fn$ from the gsubfn package string interpolation is enabled on its arguments. See ?fn for more info. This is often used with sqldf in the sqldf package but can be used with any function as we show here. In particular inserting $variable into a string argument of the function call substitutes the value of that variable into that string:

library(gsubfn)
lvls <- toString(shQuote(levels(iris$Species)))

fn$sqlQuery(abcd,  "select * from TestTableName1 where DESCRIPTION in ($lvls)")

or if we want to examine the string first:

sql <- fn$identity("select * from TestTableName1 where DESCRIPTION in ($lvls)")
cat(sql, "\n") # look at sql string

sqlQuery(abcd, sql)

The output from the cat statement is:

select * from TestTableName1 where DESCRIPTION in ("setosa", "versicolor", "virginica")

Upvotes: 1

Related Questions