In R how to make a string of fields in to a SQL join

In R given a string like “fld1,fld2” how can I get something like “b.fld1=v.fld1 and b.fld2=v.fld2” for a SQL query. The string may have up to 10 distinct elements. This is for trying to build a join on the fly. b and v are the aliases for two tables. There willo only be two tables.

Answer

String interpolation with stringr::str_glue is a convenient way of achieving this:

library(stringr)
sql_join <- function(columns, table1, table2) {
  columns_vec <- str_split(columns, ",")[[1]]
  str_glue("{table1}.{columns_vec} = {table2}.{columns_vec}") %>%
    str_c(collapse = " AND ")
}

sql_join("fld1,fld2", "b", "v")
#> [1] "b.fld1 = v.fld1 AND b.fld2 = v.fld2"
sql_join("fld1,fld2,fld3,fld4", "foo", "bar")
#> [1] "foo.fld1 = bar.fld1 AND foo.fld2 = bar.fld2 AND foo.fld3 = bar.fld3 AND foo.fld4 = bar.fld4"