r: escape single quote in a string

Question

I have a string like a <- "abc'def" and I need to paste this into a longer sql string:

 s <- paste0("select * from x where xx ='", a, "'")

But the single quote in the middle makes it fail, I cannot manually replace ' with \' but need to use a function.

In SQL, single quotes are escaped by doubling them: http://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server — sebastian-c, Oct 26 '16 at 09:02

score 3 · Answer 1 · answered Oct 26 '16 at 08:29

3

You need to escape the ' in a with something appropriate to your database, not to R. That will probably be by doubling it up: ''.

answered Oct 26 '16 at 08:29

Hong Ooi

score 3 · Answer 2 · answered Oct 26 '16 at 09:04

3

In many SQL implementations, doubling achieves the escaping you want:

a <- "abc'def"
a <- gsub("'", "''", a)
s <- paste0("select * from x where xx ='", a, "'")

[1] "select * from x where xx ='abc''def'"

answered Oct 26 '16 at 09:04

sebastian-c

2 Answers2