Styjun

Did Shadowfax go to Valinor?

How to test if a transaction is standard without spending real money?

Can divisibility rules for digits be generalized to sum of digits

What does "Puller Prush Person" mean?

Can a Warlock become Neutral Good?

Theorem, big Paralist and Amsart

How to find program name(s) of an installed package?

Why did the Germans forbid the possession of pet pigeons in Rostov-on-Don in 1941?

What would happen to a modern skyscraper if it rains micro blackholes?

What is the word for reserving something for yourself before others do?

Is it legal for company to use my work email to pretend I still work there?

What defenses are there against being summoned by the Gate spell?

Why, historically, did Gödel think CH was false?

How do I create uniquely male characters?

What's the output of a record cartridge playing an out-of-speed record

If I cast Expeditious Retreat, can I Dash as a bonus action on the same turn?

Why Is Death Allowed In the Matrix?

Have astronauts in space suits ever taken selfies? If so, how?

How does one intimidate enemies without having the capacity for violence?

Test whether all array elements are factors of a number

I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine

Font hinting is lost in Chrome-like browsers (for some languages )

Show that if two triangles built on parallel lines, with equal bases have the same perimeter only if they are congruent.

Arthur Somervell: 1000 Exercises - Meaning of this notation

How to filter by a string containing variables in dbplyr

Pass SQL functions in dplyr filter function on databaseHow to sort a dataframe by multiple column(s)How to make a great R reproducible exampleHow do I supress sqldf messages in R Markdown?dbplyr::in_schema case sensitiveDoes sql_variant in dbplyr work as it should?Complicated filter statements in dbplyr using lists of listsHow to do a floor_date() in dbplyrSetting a seed with dbplyrdbplyr copy_to truncates strings with accented wordsdbplyr on postgresql count missing

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

3

3

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

r dplyr dbplyr

share|improve this question

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  What flavor of SQL? Wrapping the call in do sometimes works.
  
  – alistaire
  Sep 7 '17 at 12:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MS SQL Server. Can you explain do or link to an example?
  
  – Alex
  Sep 7 '17 at 13:05
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 7 '17 at 13:20
  

  
  
  
  

add a comment | 

3

3

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

r dplyr dbplyr

share|improve this question

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  What flavor of SQL? Wrapping the call in do sometimes works.
  
  – alistaire
  Sep 7 '17 at 12:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MS SQL Server. Can you explain do or link to an example?
  
  – Alex
  Sep 7 '17 at 13:05
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 7 '17 at 13:20
  

  
  
  
  

add a comment | 

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

r dplyr dbplyr

share|improve this question

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

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

share|improve this question

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

share|improve this question

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

edited Sep 16 '17 at 11:02

Moody_Mudskipper

24.8k33571

asked Sep 7 '17 at 12:55

AlexAlex

6401924

asked Sep 7 '17 at 12:55

AlexAlex

6401924

3 Answers
3

active

oldest

votes

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

share|improve this answer

answered Sep 8 '17 at 11:19

chrk623chrk623

414

add a comment | 

1

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

share|improve this answer

answered May 8 '18 at 18:18

nickvnickv

112

add a comment | 

0

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

share|improve this answer

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f46096942%2fhow-to-filter-by-a-string-containing-variables-in-dbplyr%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

answered Sep 8 '17 at 11:19

chrk623chrk623

414

add a comment | 

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

share|improve this answer

answered Sep 8 '17 at 11:19

chrk623chrk623

414

add a comment | 

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

eg..

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

share|improve this answer

answered Sep 8 '17 at 11:19

chrk623chrk623

414

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

share|improve this answer

answered Sep 8 '17 at 11:19

chrk623chrk623

414

answered Sep 8 '17 at 11:19

chrk623chrk623

414

answered Sep 8 '17 at 11:19

chrk623chrk623

414

1

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

share|improve this answer

answered May 8 '18 at 18:18

nickvnickv

112

add a comment | 

1

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

share|improve this answer

answered May 8 '18 at 18:18

nickvnickv

112

add a comment | 

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

share|improve this answer

answered May 8 '18 at 18:18

nickvnickv

112

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

share|improve this answer

answered May 8 '18 at 18:18

nickvnickv

112

answered May 8 '18 at 18:18

nickvnickv

112

answered May 8 '18 at 18:18

nickvnickv

112

0

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

share|improve this answer

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

add a comment | 

0

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

share|improve this answer

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

add a comment | 

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

share|improve this answer

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

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

share|improve this answer

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689

answered Mar 21 at 23:48

mrjoh3mrjoh3

1689