grepl for dplyr sql table?

Question

is there a workaround to use something like

filter(df, grepl("A|B|C",location))

for a dplyr SQL table? In SQL it is probalby a LIKE. Of cource I could convert the SQL table to a R data table, but it is very large. (http://cran.r-project.org/web/packages/dplyr/vignettes/databases.html) At the moment I get

Error in sqliteSendQuery(conn, statement) : 
  error in statement: no such function: GREPL

thx Christof

`filter(df, location %in% c("a", "b", "c")`, or use a `semi_join()` — hadley, Dec 14 '14 at 13:37
@hadley thx for your brilliant libraries. Is there a way to get also pattern matching (SQL LIKE instead of IN)? `%in%`and `semi_join()`do exact matching, if I understood it correctly — ckluss, Dec 14 '14 at 15:53
@dorvak `?translate_sql`or direct http://cran.r-project.org/web/packages/dplyr/index.html dplyr.pdf p.65 — ckluss, Jan 09 '15 at 17:36
`x %like% "y"` will match whole variable, while `x %like% "%y%"` will grep for pattern y in column x. — Veera, Sep 13 '17 at 11:57

score 3 · Answer 1 · answered Nov 14 '17 at 14:29

Using sql to translate the expression directly into sql is one option.

sql_table %>% filter( sql("location LIKE 'A%' 
                           OR location LIKE 'B%' 
                           OR location LIKE 'C%'")

Which will inject the following into the WHERE statement of your query:

<SQL> location LIKE 'A%' OR location LIKE 'B%' OR location LIKE 'C%'

1 Answers1