Specifying an SQL where statement based on a string in R (using rodbc)

Question

This is my first attempt at using R to access data from within MS Access using ODBC.

The following query works:

id <- levels(assetid)[assetid[,1]][12]

qry <- "SELECT DriverName FROM Data WHERE ID = 'idofinterest'"
sqlQuery(con, qry)

However, I would like to know if there is a way to use the variable "id" in the "qry" statement (without using paste)? I have seen some statements on the web with $ and % signs - however I haven't had any success in using them.

Thanks.

Hong Ooi · Accepted Answer · 2013-06-06 08:50:42Z

1

Why don't you want to use paste? Anyway, sprintf is an alternative means of string munging.

qry <- sprintf("SELECT DriverName FROM Data WHERE ID = '%s'", id)
sqlQuery(con, qry)

answered Jun 6, 2013 at 8:50

Hong Ooi

57.9k13 gold badges145 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G. Grothendieck · Accepted Answer · 2013-06-06 11:07:09Z

1

Try fn$ from the gsubfn package :

> library(gsubfn)
> id <- "abc"
> fn$identity("SELECT DriverName FROM Data WHERE ID = '$id'")
[1] "SELECT DriverName FROM Data WHERE ID = 'abc'"

answered Jun 6, 2013 at 11:07

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Collectives™ on Stack Overflow

Specifying an SQL where statement based on a string in R (using rodbc)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related