0

I am trying to run a huge boolean sqldf in for a local dataframe in R like so:

sqldf("UPDATE df SET STORE_FLAG = 1
WHERE CONTENTS RLIKE '(([[:<:]]store[[:>:]]))'", drv='SQLite')

Query abbreviated above. The actual query is 20 words long with near functions

However, I have a syntax error using the RLIKE function:

Error in sqliteSendQuery(con, statement, bind.data) : 
error in statement: near "RLIKE": syntax error

Is there a way to use RLIKE with the sqldf library?

lmcshane
  • 1,074
  • 4
  • 14
  • 27
  • Not a direct answer, but have you considered just using native R structures (or better yet `data.table`) instead of SQLite? What's the size/structure of the data? – arvi1000 Jul 15 '15 at 15:17
  • @arvi1000 Data is around 300,00 rows with about 8 variables. I haven't considered data.table because replicating the sql query seems more difficult with lots of near functions. But maybe I am missing something? – lmcshane Jul 15 '15 at 15:25
  • Oh yeah, piece of cake for `data.table` size-wise. I'm not familiar with the exact specs of `near`, but I'd be shocked if there weren't a way to replicate it in R. Might be worth a new post about that – arvi1000 Jul 15 '15 at 15:36
  • The `drv=` argument in the question is specifying that sqlite is to be used so that is what it is using, not MySQL. – G. Grothendieck Jul 15 '15 at 20:53

0 Answers0