r column values in sql where statement

Question

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

`sqlQuery` doesn't come from the `sqldf` package. It comes from the `RODBC` package. Also, you have two `where` in your select statement. — LyzandeR, Feb 18 '15 at 22:33
And you can try this for your query: `strwrap(paste("Select * from TestTableName1 where WHERE DESCRIPTION IN (" , paste( unique(iris$Species), collapse=' ,') , ")"))` — LyzandeR, Feb 18 '15 at 22:41

Gregor Thomas · Accepted Answer · 2022-02-16T20:27:38.253

5

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)"

edited Feb 16 '22 at 20:27

answered Feb 18 '15 at 22:41

Gregor Thomas

136,190
20
167
294

Are you adding `>` in the beginning of the lines on purpose? I have a great urge to edit this answer... – David Arenburg Feb 18 '15 at 22:45
Just copy/pasting from my console, being lazy. Edits welcome. (Though I suppose I could take the time myself.) *Edit:* beat me to it, thanks! – Gregor Thomas Feb 18 '15 at 22:49
this is returning a list without "" around values. It won't work in the SQL Where clause. – Giacomo Feb 16 '22 at 16:58
1

@Giacomo edited to add quotes. – Gregor Thomas Feb 16 '22 at 20:27

score 1 · Answer 2 · answered Feb 19 '15 at 02:50

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")

r column values in sql where statement

2 Answers2

Linked