sqldf RLIKE function

Question

I have the following mySQL query:

"select * from data WHERE TEXT RLIKE '[[:<:]]digital[[:>:]]' OR TEXT RLIKE '[[:<:]]data[[:>:]]' OR TEXT RLIKE '[[:<:]]agile[[:>:]]' OR (TEXT RLIKE '[[:<:]]self[[:>:]]' AND TEXT RLIKE '[[:<:]]service[[:>:]]') OR TEXT RLIKE '[[:<:]]cloud[[:>:]]' OR TEXT RLIKE '[[:<:]]insight[^[:space:]]+[[:>:]]' OR TEXT RLIKE '[[:<:]]modern[^[:space:]]+[[:>:]]'")

I want to apply this in R using the sqldf package:

sqldf( "select * from data WHERE TEXT RLIKE '[[:<:]]digital[[:>:]]' OR TEXT RLIKE '[[:<:]]data[[:>:]]' OR TEXT RLIKE '[[:<:]]agile[[:>:]]' OR (TEXT RLIKE '[[:<:]]self[[:>:]]' AND TEXT RLIKE '[[:<:]]service[[:>:]]') OR TEXT RLIKE '[[:<:]]cloud[[:>:]]' OR TEXT RLIKE '[[:<:]]insight[^[:space:]]+[[:>:]]' OR TEXT RLIKE '[[:<:]]modern[^[:space:]]+[[:>:]]'")

that I want to run against some sample data:

data <- structure(list(Participant = 1:3, A = c("and other agile digital, things", "testing test and modern", "nothing here"), B = c("", "b", "b"), C = c("c", "c", "c")), .Names = c("Participant", "TEXT", "other", "another"), row.names = c(NA, -3L), class = "data.frame")

I get the following error

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

It seems like this package doesn't like RLIKE statements. Why, and how do I get around this?

Pls specify clearly what you are asking. The command is 'like' not Rlike. — Chirayu Chamoli, Nov 03 '16 at 07:12
@ChirayuChamoli the MySQL syntax I would like to use is RLIKE http://dev.mysql.com/doc/refman/5.7/en/regexp.html — lmcshane, Nov 03 '16 at 07:17
Take a look at [this](http://stackoverflow.com/questions/33026213/regarding-sqldf-package-regexp-function-in-r). — Chirayu Chamoli, Nov 03 '16 at 07:27

score 2 · Answer 1 · answered Nov 03 '16 at 07:19

It is always good to include the desired result. Based on this source it looks like you want to extract the rows where in the TEXT-column foo is followed by a non-space character.

You can also do this quite simply in R with grep-function:

dat[grep('.*(foo)[^ ]', dat$TEXT),]

which gives:

  Participant                  TEXT other another
1           1 and other foo, things             c

_{PS: it is better not to give your dataset the same name as a function; for that reason I used dat instead of data}

sqldf RLIKE function

1 Answers1