Menu
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.
960
Did you know that you can run SQL code in an R Notebook code chunk? To use SQL, open an R Notebook in the RStudio IDE under the File > New File menu. Start a new code chunk with {sql} , and specify your connection with the connection=con code chunk option.
Not only can you easily retrieve data from SQL Sources for analysis and visualisation in R, but you can also use SQL to create, clean, filter, query and otherwise manipulate datasets within R, using a wide choice of relational databases. There is no reason to abandon your hard-earned SQL skills!
It could be this using the SUBSTR function in MySQL: SELECT `name` FROM `students` WHERE `marks` > 75 ORDER BY SUBSTR(`name`, -3), ID ASC; SUBSTR(name, -3) will select the last three characters in the name column of the student table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
