CODEWITHSUNDEEP
mysqlrmariadbtidyverse
Mutuelinvestor
Mutuelinvestor

Reputation: 3528

How do you escape an Apostrophe in R so you can insert the string into a MySQL table

I'm inserting some strings into a MYSQL table via an R script. Some of the strings include apostrophes that need to be escaped prior to inserting into the table.

Given the following string:

myStr <- "AIN'T NO ELMERS"

I have tried the following alternatives with no luck:

str_replace(horse,'(\w+)(’)(.*)','\1\\\2\3')) ==> results in an error message

str_replace(horse,'(\w+)(\’)(.*)','\1\\\2\3')) ==> no error but the inserted string is AIN’T NO ELMERS

How does one escape successfully escape the apostrophe in the string?

My r script takes the following form:

library(DBI)
library(odbc)
library(RMariaDB)

insert <- dbSendQuery(con, "INSERT ignore INTO names(`name`, `value`) VALUES (?, ?)")
dbBind(insert, names)
dbClearResult(insert)
con <- dbDisconnect(con)

Upvotes: 2

Views: 885

Answers (2)

Mutuelinvestor
Mutuelinvestor

Reputation: 3528

The issue was resolved by ensuring the utf8 character set was utilized.

As a first step, use the following commands in your MYSQL database to ensure the database, table and columns are using utf8:

USE dbname;  SELECT @@character_set_database;
SHOW FULL COLUMNS FROM dbname.tablename;

You can alter database, table and colunms with ALTER

ALTER TABLE `dbname`.`tablename` 
CHANGE COLUMN `colname` `colname` VARCHAR(10) CHARACTER SET 'utf8' NULL DEFAULT NULL ;

Next, use the following commands to ensure your insert statement is also utilizing utf8:

con <- dbConnect(odbc::odbc(), "MySQL")
dbSendQuery(con, 'set character set "utf8"')

With this, apostrophes should be inserted into your table correctly.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

The usual way to escape single apostrophes on the MySQL side is to just double them up, i.e. use ''. So try this version:

myStr <- "AIN''T NO ELMERS"

Upvotes: 4

Related Questions