Can I gracefully include formatted SQL strings in an R script?

Question

I'm working in an R script that uses a long SQL string, and I would like to keep the query relatively free of other markup so as to allow copying and pasting between editors and applications. I'd also like the ability to split the query across lines for better readability.

In the RODBC documentation, the paste function is used to build the query out of separate chunks, but I'd prefer something less kludgy and with fewer quotes and commas. Thanks for your help.

Answer 1

If you're an old C programmer from way back, as I am, you might enjoy just using sprintf().

Borrowing Ian's example:

y<-"y1"
x<-"somethingorother"
query <- sprintf(
'SELECT DISTINCT x AS %s,
                 y AS %s,
 FROM tbl
 WHERE id=%%s
 AND num=%%d', x, y)

yields:

> cat(query,"\n")
SELECT DISTINCT x AS somethingorother,
                 y AS y1,
 FROM tbl
 WHERE id=%s
 AND num=%d