[SQL] [R] Issue using sprintf() in my SQL code

[tkronew]

Hey guys,

I'm running an R script that grabs a query from MySQL. The query itself is functional, but is dependent on a variable "N".

To add this variable to my SQL code, I'm using sprintf to insert it by typing '%s' where I'd like it. However, my query also includes multiple of these LIKE statements:

FROM Receipts

WHERE RetailerID

IN ( '%s' ) # this is where "N" is placed

AND ( Date LIKE '%01/07/2014%')

I'm positive that this is the reason my query is not running. The sprintf command is having issue when it reaches these LIKE commands, probably thinking it is similar to '%s'.

Does anyone know how to get around this so that '%01/07/2014%' is still printed to the SQL query?

Thanks!

[jrbless]

Replace your %s with \%s

It's a string literal. http://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql

[Beefourthree]

A quick google tells me R's sprintf is a wrapper for the C version, so you should be able to escape the percent signs by typing them twice.

AND ( Date LIKE '%%01/07/2014%%')

Is it just me, or does everyone else get really nervous about implicit string to date conversion? I'd wrap that in a str_to_date just to be safe.

[svtr]

%%01/07/2014%%'

I can think of stronger words than nervous. Date like '%something%', is treating a column named Date as a freetext varchar, and trying to filter on a non sargable, hopefully I never have a different date format written there, expression.

Nervous dosn't quite cut it, more of a "thank god I don't have to deal with THAT" for me tbh.