CODEWITHSUNDEEP
rsqldf
brucezepplin
brucezepplin

Reputation: 9752

r sqldf escape double quotes

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?

Upvotes: 3

Views: 2486

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269346

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

Upvotes: 0

AEF
AEF

Reputation: 5650

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.

Upvotes: 4

Related Questions