CODEWITHSUNDEEP
sqlrsqldf
tricky
tricky

Reputation: 1553

Escaping an apostrophe in Sqldf with R

I have a dataframe mergeTest which have a column Name with apostrophes on few values,

So I'm looping through it to divide values which denominator are contained in the table nbrToDivide

test1 <- sqldf(c(paste('UPDATE mergeTest SET Value = Value/',nbrToDivide[i],
            ' WHERE `Year` =',nbrToDivide$`Year`[i],
            ' AND UPPER(Name) = \'',nbrToDivide$Name[i],sep=""),
    'SELECT * from mergeTestt'))

The problem is when the value of UPPER(Name) contains an apostrophe in it, it will interprete it and return an error.

I tried to use gsub with grepl but it adds two backslashes to my names so I dunno if there is a way to deal with it or should I just suppress the apostrophe in my two datraframes ?

Upvotes: 1

Views: 971

Answers (2)

Virag Swami
Virag Swami

Reputation: 197

Replacing one single quote with two single quotes will fix this as in :

"SELECT * FROM TableName WHERE FieldName = 'QueryString''s Value'"

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 269346

Double the single quote. Here is an example:

> sqldf("select 'O''Brian' Name")
     Name
1 O'Brian

Upvotes: 4

Related Questions