CODEWITHSUNDEEP
sqlrdplyrpaste
Nancy
Nancy

Reputation: 4079

dplyr to paste columns together in remote DB

I'd like to paste two columns together in a remote database using dplyr. I've tried something like this:

mydb %>%
    select(A, B) %>%
    mutate(newcol = paste(A, B, collapse = "_"))

But I get an error saying Error in paste(A, B, collapse = "_") : object 'A' not found . What is dplyr's translation of paste?

I tried: 

> translate_sql(paste(x, y, sep = "_"))
<SQL> PASTE("x", "y", '_' AS "sep")

So I plugged that syntax in to attempt:

mydb %>%
    select(A, B) %>%
    mutate(newcol = PASTE("A", "B", "_" AS "sep"))

but using that didn't work either, with the error of Named arguments ignored for SQL PASTE. Thoughts?

Upvotes: 4

Views: 701

Answers (1)

vdep
vdep

Reputation: 3590

paste() concatenates only list of elements separated by collapse's value. since you have only one value here, the correct way is to use sep. Also whole column is considered in paste(), so group_by is used to separate them. 

mydb %>%
    group_by(A, B) %>%
    mutate(newcol = paste(A, B, sep = "_"))

Upvotes: 1

Related Questions