3

I need to insert a data frame into a SQL database. I've build the script (using loops, str_c, RODBC) to transform my data frame into a SQL Insert command, but I've run into the problem with a single "'" breaking the SQL.

Here is an example of the problem:

The Data Frame looks like this:

pk  b
1    o'keefe

The desired SQL output is: INSERT INTO table (pk, b) (1, 'o\'keefe')

gsub("'", "\'", str_replace_na(df$b[1], ""))

[1] "o'keefe"

gsub("'", "\\\\'", str_replace_na(df$b[1], ""))

[1] "o\\'keefe"

I've tried str_replace, str_replace_all, gsub w/ fixed = TRUE and perl = TRUE and I get the same result.

I am aware of the comment on How to give Backslash as replacement in R string replace, which states cat() shows the slash. But this doesn't carry over to my data frame or SQL query.

Any help on this problem would be greatly appreciated!

Additional note, I am aware the R prints a double backslash as referenced http://r.789695.n4.nabble.com/gsub-replacing-double-backslashes-with-single-backslash-td4453328.html and R: How to replace space (' ') in string with a *single* backslash and space ('\ ') even though only one slash really exists. However, my SQL statement still won't work when zero or two backslashes are present.

prosoitos
  • 6,679
  • 5
  • 27
  • 41
Marcus Evans
  • 31
  • 2
  • Standard SQL requires that you double a single quote within single quotes. C style backslash is not part of standard SQL (although it is possible that the database you are using but did not mention supports it as an extension of standard SQL).. – G. Grothendieck Oct 27 '18 at 17:12
  • Sad to say I'm not using Standard SQL, I'm using FileMaker Pro's SQL which requires a single quotation mark. (See https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf, page 18). – Marcus Evans Oct 31 '18 at 21:07

2 Answers2

0

"o\\'keefe" is in fact what you want: the double blackslash is in fact a representation of a single backslash.

For instance:

\U005C is the unicode character for the backslash. Yet:

"\U005C"

[1] "\\"

While \U002F is the unicode character for the forward slash and:

"\U002F"

[1] "/"

So your second solution already gave you what you want. Removing the unnecessary str_replace_na():

gsub("'", "\\\\'", df$b[1])

[1] "o\\'keefe"

Note: credit in fact goes to @Rui Barradas who showed that the double backslash represents a single backslash with:

nchar("\\")

[1] 1
prosoitos
  • 6,679
  • 5
  • 27
  • 41
-1

Try putting the single quote inside ['].

x <- "o'keefe"
y <- gsub("[']", "\\\\'", s)
y
#[1] "o\\'keefe"

This seems to have added two characters to the string but no, there is just one \.

nchar(x)
#[1] 7

nchar(y)
#[1] 8
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
  • 2
    You get the same result without the square brackets. `nchar(gsub("'", "\\\\'", df$b[1]))` is also `8`. – prosoitos Oct 27 '18 at 06:26
  • So I think that you got a point there that the OP's second result (showing 2 \ instead of one) might in fact be fine and work in the SQL. But the added brackets are unnecessary. In which case, doing `gsub("'", "\\\\'", df$b[1])` would be sufficient. – prosoitos Oct 27 '18 at 06:29
  • @prosoitos You are right, the brackets are only needed with metacharacters (and `fixed = FALSE`, the R default). – Rui Barradas Oct 27 '18 at 07:44
  • 1
    I don't use SQL, so I couldn't test whether the double \ was in fact only one, but in addition to your clever `nchar()` test, I tried to run `"\U005C"` and it does indeed give `[1] "\\"` (while, for instance, `"\U002F"` give `[1] "/"`) :) So you are definitely right about that :). So the answer is that the OP had already found the solution with his 2nd attempt (except that `str_replace_na()` was unnecessary). – prosoitos Oct 27 '18 at 16:17
  • But I feel bad posting that as an answer because you deserve the credit for realizing that \\ was only one \ (even if your bracket added noise to the answer). – prosoitos Oct 27 '18 at 16:45
  • 1
    @prosoitos If you post it, I will delete mine. – Rui Barradas Oct 27 '18 at 16:51
  • Thanks @Rui Barradas. I posted it. But you don't have to delete yours though. – prosoitos Oct 27 '18 at 17:05