3

I wish to run the following sql statement within sqldf():

select columnA, "new_column_value" as columnB, "column.C" from mytable
where columnA in ('123','456')

but when I run within sqldf :

sqldf('select columnA, "new_column_value" as columnB, "column.C" from mytable
    where columnA in ('123','456')')

I get the obvious error that the single quotes have escaped the sql code at in('123','456'), and if I wrap the sql code with double quotes, the sql is escaped where I create a new column "new_column_value" or when I select via literal string from the unhelpfully named "column.C".

So I am in a bit of a catch 22. It's almost as if I want to wrap by sql code in something other than quotes within the sqldf() call.

Can I escape both single and double quotes where I please?

brucezepplin
  • 9,202
  • 26
  • 76
  • 129
  • Can you try to use ` ? – Orhan Yazar Aug 09 '17 at 14:28
  • 1
    Posting the entire statement in double quotes and using single quotes inside without escaping should work. sqldf("SELECT columnA, 'new_column_value' as columnB, 'column.C' FROM mytable WHERE columnA in ('123','456')") – Sagar Aug 09 '17 at 14:39
  • Thanks Sagar was literally about to update I tried this with success – brucezepplin Aug 09 '17 at 14:40
  • Glad you tried and that worked. – Sagar Aug 09 '17 at 14:41
  • I would suggest you just learn how to use R syntax. Data manipulation in R is both straight forward and very efficient. – David Arenburg Aug 09 '17 at 14:44
  • Assuming that you are using the default sqlite backend it is not true that sqldf ignores double quotes and it is also not true (claimed in the Update) that you can refer to variable names by putting them within single quotes. See my answer for a demonstration of this. – G. Grothendieck Aug 09 '17 at 14:50
  • @G. Grothendieck - you are correct. I had wrapped 'variable' in a count statement, where the column values are used. breaks otherwise so still no answer – brucezepplin Aug 09 '17 at 14:57

2 Answers2

3

In R strings double quotes are escaped by a backslash:

test <- "String escaping is \"easy\"!"

This should work for you too. (Wrap the whole command in double quotes and escape the inner ones by a backslash)

This can also be seen if you look at intToUtf8(34) where 34 is the ASCII code for a double quote.

AEF
  • 5,408
  • 1
  • 16
  • 30
  • hi @AEF - I have tried this, and it seems using `\"CODEHERE\"` within sqldf completely ignores the doublequotes, rather than allowing it's behaviour - which in this case is to tell sql that I am selecting from a column using a literal string. – brucezepplin Aug 09 '17 at 14:27
  • That is very strange, because the escaping should happen before the strings are send to the SQL engine. Also, I just found [this][https://stackoverflow.com/questions/18686083/handling-quotation-marks-in-sqldf] question where the exact same thing works. – AEF Aug 09 '17 at 14:31
0

Assuming the default sqlite backend, either of these work:

> sqldf("select \"Time\" from BOD")
  Time
1    1
2    2
3    3
4    4
5    5
6    7

> sqldf("select [Time] from BOD")
  Time
1    1
2    2
3    3
4    4
5    5
6    7

The Update in the question claims that this works but it gives the constant string Time and not the variable of that name.

> sqldf("select 'Time' from BOD")
  'Time'
1   Time
2   Time
3   Time
4   Time
5   Time
6   Time
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • this is true, so now I have to figure out why my code works when it shouldn't :/ I'll figure out and update – brucezepplin Aug 09 '17 at 14:51
  • I happen to be using `count('Time')`. but when using `select 'Time'` as you point out I output the word Time for however many rows in the table. you are right - I'll remove my update – brucezepplin Aug 09 '17 at 14:55