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))

What flavor of SQL? Wrapping the call in `do` sometimes works. — alistaire, Sep 07 '17 at 12:58
Something like `iris %>% do(mutate(., is_setosa = grepl('set', Species)))` It's probably not the best way to do things, though; I'm sure there's a more native solution. — alistaire, Sep 07 '17 at 13:20

score 8 · Answer 1 · answered May 08 '18 at 18:18

8

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()

answered May 08 '18 at 18:18

nickv

81
1
4

score 2 · Answer 2 · answered Mar 21 '19 at 23:48

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()

answered Mar 21 '19 at 23:48

mrjoh3

437
2
11

score 1 · Answer 3 · answered Sep 08 '17 at 11:19

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))

answered Sep 08 '17 at 11:19

chrk623

76
1
5

How to filter by a string containing variables in dbplyr

3 Answers3