Remove backslashes from character string

Question

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"

Believe it or not there is no backslash character in `"select case when \"est\" dsaf"`. Please read `?Quotes` — IRTFM, Jan 28 '15 at 23:41
@Richard - I cannot change them to single quotes they need to be double. Any idea how to eliminate the "\" — user3022875, Jan 28 '15 at 23:55
The `cat` only provides a side-effect and returns a NULL object. — IRTFM, Jan 29 '15 at 00:20
Provide complete code that precedes the error. (If you are using the result from `cat`, then `test` is NULL.) — IRTFM, Jan 29 '15 at 00:32
I credited your answer. It was correct. I did not need to use cat. See bottom of edit. — user3022875, Jan 29 '15 at 00:36

IRTFM · Accepted Answer · 2020-10-08T17:20:01.210

12

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

So the was the first problem. The second problem was trying to assign the value of a cat call. The cat function always returns NULL after sending its value to a destination, possibly the console output. You cannot save the resulting character value to an R name. You always get NULL. See the ?cat help page.

edited Oct 08 '20 at 17:20

answered Jan 28 '15 at 23:48

IRTFM

258,963
21
364
487

2

Do you have an answer? – user3022875 Jan 28 '15 at 23:58
3

I gave you an answer. There is no backslash to remove. – IRTFM Jan 28 '15 at 23:59
1

I am passing this to string to SQL and it does see the "\" that why i need to remove it. – user3022875 Jan 29 '15 at 00:04
Well saying that a computer or program "sees" something is not going to be accurate, now, is it? You need to provide code and an error message. – IRTFM Jan 29 '15 at 00:08
It sure is accurate. SQL "sees" the "\". I have used cat to remove the "\" but cat also remove the doubel quotes around the sql statement and after cat(test) the class of test is NULL – user3022875 Jan 29 '15 at 00:10
2

You are repeating anthropomorphic characterizations of programmatic behavior. Please provide the requested code and error messages. – IRTFM Jan 29 '15 at 00:12
there is no error. See update. Cat removed the "\" but produces an unquoted object that I can't pass to SQL – user3022875 Jan 29 '15 at 00:19
1

Right. That's what `cat` is documented to return. You still have not provided any evidence that whatever instance of "SQL" is being communicated with is producing an error related tot he presence of a backslash in the test object. – IRTFM Jan 29 '15 at 00:22
See edit. 'getCharCE' must be called on a CHARSXP – user3022875 Jan 29 '15 at 00:23

Ted · Answer 2 · 2017-07-17T02:40:16.083

0

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"

edited Jul 17 '17 at 02:40

answered Jul 17 '17 at 01:39

Ted

91
1
4

Remove backslashes from character string

2 Answers2

Linked

Related