How to filter by a string containing variables in dbplyr

Question

I normally use filter with grepl in dplyr, but when using dbplyr. I get an error that grepl is not a recognized function. My guess is that it can't translate to SQL server. What is a way around this with dbplyr

Here is a reproducible example

library(dbplyr)
library(nycflights13)

## Working chunk
con <-DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "flights", flights)
DBI::dbGetQuery(con, "SELECT origin, flight 
FROM flights WHERE origin like '%jf%'")
## End working chunk

## The below code does not work 
flights <- tbl(con,"flights")
flights %>% 
  select(origin, flight) %>% 
  filter(grepl('jf', origin))

Answer 1

Im not quiet sure what your asking but have u tried any at functions?

eg..

mtcars %>% mutate_at(vars(matches("cyl")), funs("123" = .+1))

Answer 2

I frequently use grepl with a pipe to match multiple values. For postgresql If you want to match multiple values similar to will also work:

dplyr::tbl(con, "flights") %>% 
    filter(origin %similar to% '(JF|LG)%') %>%
    collect()

Answer 3

I found the solution from this answer effective.

Here is the code that works for your case:

dplyr::tbl(con, "flights") %>% 
    filter(origin %like% '%jf%') %>%
    collect()