MW Frost MW Frost - 1 month ago 9
R Question

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

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

you can override the %+% operator to have better string concatination syntax:

'%+%' <- function(x,y) paste(x,y,sep="")

y<-"y1"
x<-"somethingorother"
query<-
'SELECT DISTINCT x AS ' %+% x %+%',\n'    %+%
'                y AS ' %+% y %+% '\n'    %+%
' FROM tbl
 WHERE id=%s
 AND num=%d'

cat(query,"\n")

yields:

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