Regarding sqldf package/regexp function

Question

I am using sqldf package and sql analyze one table generated by a classification model.

But when I use the code:

table<-sqldf("
SELECT a,
b, 
c, 
d, 
e, 
f,
CASE WHEN (REGEXP_LIKE(t, '\b(2nd time|3rd time|4th time)\b')) = TRUE
THEN 1 ELSE 0 END AS UPSET_NOT_LIKE,
regexp_extract(t, '\b(2nd time|3rd time|4th time)\b')) as Word
FROM cls                  
")

It looks like that the sqldf package don't have regexp_like and regexp_extract function.

Is there any sql-advanced packages that I can use to do the query?

The available SQL functions depend on the backend you have connected. By default this is SQLite, but if you have a favorite brand of SQL then install the drivers and attach. — IRTFM, Oct 08 '15 at 21:34
Hi @BondedDust, how can I install drivers and attach? you mean I need to connect R to a sql server to do the regexp? I'm currently getting my data from a netezza database, should I connect and upload data to the data base to do so? — WayToNinja, Oct 08 '15 at 22:25
Perhaps this will help: http://stackoverflow.com/questions/22302411/connect-r-and-netezza-using-rjdbc — IRTFM, Oct 09 '15 at 18:36

score 3 · Answer 1 · edited Nov 03 '16 at 07:50

sqldf works with SQLite, h2, mysql and postgresql backends.

The default is SQLite and it does not support those functions. SQLite does support the regexp keyword if SQLite was compiled with support but I don't think the driver in the RSQLite package has done so.

If you use the postgreSQL database backend to sqldf then the ~ operator is available to do regexp matching and regexp_matches is available to extract matches.

See FAQ#12 on the sqldf home page for info on using the postgreSQL backend with sqldf.

See here for info on regular expression matching in postgreSQL.

In the future please provide complete minimal self contained reproducible examples in your questions which in this case means also providing a sample of the rows of cls, e.g. dput(head(cls)).

Regarding sqldf package/regexp function

1 Answers1

Linked

Related