Search code examples
rdplyrstringrroracle

Dplyr filter with str_detect returns empty tibble

I have a dplyr query in R that filters with str_detect to only get the cases that begins with the letters "KS", but it returns an empty tibble. I´m connected to an Oracle database using ROracle. 

table <- tbl(con, "TABLE")

table %>% 
  filter(str_detect(COLUMN, "^KS"))

If I however use collect() to generate the tibble, it works: 

table <- collect(tbl(con, "TABLE"))

table %>% 
  filter(str_detect(COLUMN, "^KS"))

Why is that? And how can I get it working without the collect? Some of the tables I need are to large to collect.

Update: If I change it to filter for a specific column value, like this: table %>% filter(str_detect(COLUMN, "^KS")), it works. For some reason the regex doesn´t work without the collect().

Solution

  • Richard Telford pointed me in the right direction with the link in the comments. It works if I instead use: 

    table <- tbl(con, "TABLE")

table %>% 
  filter(COLUMN %like% "%KS%")