use character string in R inside SQL WHERE IN statement having issues with LIKE statement-CodePudding

I have a query similar like the one below where I need to use a string of characters and it works okay but not when I use the LIKE statement. Please read further below

ids
 [1] "0000000000000000010000001" "0000000000000000010000002" "0000000000000000010000003"
 [4] "0000000000000000010000004" "0000000000000000010000005" "0000000000000000010000006"
 [7] "0000000000000000010000007" "0000000000000000010000008" "0000000000000000010000009"
[10] "0000000000000000010000010"


expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s)", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

So above code works but code below does not when I add the LIKE statement. I want to find all placements that begins in FR hence why using LIKE 'FR%' but I think it is interferring with the IN statement. The error I am getting is 'too few arguments'. How could I correct this issue please?

ids
 [1] "0000000000000000010000001" "0000000000000000010000002" "0000000000000000010000003"
 [4] "0000000000000000010000004" "0000000000000000010000005" "0000000000000000010000006"
 [7] "0000000000000000010000007" "0000000000000000010000008" "0000000000000000010000009"
[10] "0000000000000000010000010"

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s) and FSAS.placement LIKE 'FR%'", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

CodePudding user response：

To get a literal % in a sprintf format string double it. Also toString can be useful here.

s <- "select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s) and FSAS.placement LIKE 'FR%%'"

sprintf(s, toString(sQuote(ids, FALSE)))