Odtnhj

I am reading text in from a txt file and pass the contents to SQL. The SQL text contains double quotes and is causing problems. I would like to remove the "" in the string below so I can send it to SQL

 test<- "select case when "est" dsaf"
 test<- cat(test, sep="")
 class(test)

returns an UNQUOTED null object

> test<- "select case when "est" dsaf"
> test<- cat(test, sep="")
select case when "est" dsaf
> class(test)
[1] "NULL"

When I pass the unquoted string to SQL I get this error:

Error in odbcQuery(channel, query, rows_at_time) : 
 'getCharCE' must be called on a CHARSXP

and I would like it to return with the leading and trailing quotes then I can send it on to SQl and it will work.

[1] "select case when "est" dsaf"

Perhaps you would like to see a different representation of the same string:

test2 <- 'select case when "est" dsaf'
test<- "select case when "est" dsaf"
identical(test, test2)
#[1] TRUE

When a character value is built with double quotes, any interior instances of " become only double-quotes. They will be displayed by print (and by the REPL that you see in an interactive session) with the escape-backslash, but using cat you cant determine that they are not really in there as backslashes.

Further proof:

> nchar(""")
[1] 1

You can use either cat or print with quote=FALSE in you want to display the value as it really exists internally:

> print(test, quote=FALSE)
[1] select case when "est" dsaf

This is evidence that at least one version of "SQL" agrees (or "accepts") that there is no backslash when " appears in the interior of a string:

> require(sqldf)
Loading required package: sqldf
Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
Loading required package: DBI
> ?sqldf
> a1r <- head(warpbreaks)
> a1s <- sqldf("select * from warpbreaks limit 6")
Loading required package: tcltk
> a2s <- sqldf("select * from CO2 where Plant like 'Qn%'")
> 
> a2sdq <- sqldf("select * from CO2 where Plant like "Qn%"")
> identical(a2s,a2sdq)
[1] TRUE

The solution in R - gsub replacing backslashes worked for me.

Example:

library(stringr)
df$variable <- str_replace(df$variable,"\\","")

df$variable before: "xyz"
df$variable after:"xyz"