RMySQL adding spaces to query making it fail

Question

I am trying to run a MySQL query using RMySQL which includes a LIKE and a custom variable inside the query.

Here is an example of my query

customvar= 'some text'
(simpleQuery<-paste("SELECT * FROM `table` WHERE NameOfField like '%",customvar,"%'")) 
res<-dbGetQuery(con, simpleQuery)

The thing is that for some reason simpleQuery interprets the query like this

[1] "SELECT * FROM `table` WHERE NameOfField '% some text %'"

Note the spaces before and after some text those are preventing my query to get proper results

I have no idea why those spaces are being generated. I tested my query without those spaces and it does give back results, so I am sure that those spaces are making my query fail.

Any idea of a way around this issue?

Versions: R 2.14.0 / RMySQL 0.8

Thanks in advance

Answer 1

The default in paste is sep=' ' (space), but you probably intended sep='' (empty string). In recent R you can use paste0 instead.

Answer 2

You get gsubfn and solve this problem by doing this at the command line (not in R):

wget http://cran.r-project.org/src/contrib/Archive/gsubfn/gsubfn_0.5-7.tar.gz
rm gsubfn_0.6-3.tar.gz

This installs a version of gsubfn that is compatible with 2.14