Home How to remove quotes around string using glue_sql() and sub() inside a loop?

Questions

How to remove quotes around string using glue_sql() and sub() inside a loop?

December 17, 2021

My code sample :

fields <- data.frame(names=c("A", "B"))

variables <- c()

for (i in 1:nrow(fields)) {
 
  variables[[i]] <- fields$names[i]
  
  query <- glue_sql("UPDATE schema1.table1 a SET {variables } = (
                      SELECT {variables} FROM schema2.table2 b WHERE b.id_obs = a.id_obs)", .con = pool)

Output:

<SQL> UPDATE schema1.table1 a SET 'A' = (
SELECT 'A' FROM public.test1 b WHERE b.id_obs = a.id_obs)
<SQL> UPDATE schema1.table1 a SET 'B' = (
SELECT 'B' FROM public.test1 b WHERE b.id_obs = a.id_obs)

I’m wondering how can I remove the quotes around my variable_names (A and B) in the output ?

I tried to add query <- sub("('.*?')", variables, query) but I don’t really understand the behaviour of sub() within my loop…

Could someone help me out please ?

>Solution :

You can use gsub instead:

query <- gsub("'", '',glue_sql("UPDATE schema1.table1 a SET {variables } = (
                  SELECT {variables} FROM schema2.table2 b WHERE b.id_obs = a.id_obs)", .con = pool) )