Dplyr filter () with SQL-like% wildcard%

Question

Suppose I have the following:

library(dplyr)
foo <- data.frame(Company = c("company1", "foo", "test", "food"), Metric = rnorm(4, 10))
> foo
   Company    Metric
1 company1 10.539970
2      foo  9.487823
3     test  9.663994
4     food  9.499327

Why foo %>% dplyr::filter(Company %like% "%foo%")returns 0 results? (instead of 2nd and 4th rows) I am trying to use the SQL equivalent wildcard filter for a specific input string for dplyr::filter.

What am I doing wrong?

Answer 1

You can use:

filter(foo, grepl("foo", Company, fixed = TRUE))

Output:

  Company    Metric
1     foo  9.906805
2    food 10.464493

As Dhawal Kapil noted, I think that %like%- data.table :

library(data.table)
DT <- data.table(foo)
DT[Company %like% 'foo']

Output:

   Company    Metric
1:     foo  9.906805
2:    food 10.464493

Answer 2

Figured it out; I will leave this in case this helps someone else in the future:

foo <- foo %>% dplyr::filter(Company %like% "foo")

" foo data.frame. , % SQL (, %foo foo%), , .

Answer 3

You can use with library (stringr)

library(dplyr)
library(stringr)
foo <- data.frame(Company = c("company1", "foo", "test", "food"), Metric = rnorm(4, 10))

foo %>% filter(str_detect(Company,"foo"))

as well as any other regular expression

foo %>% filter(str_detect(Company,"^f"))