SQL statement with multiple keywords in string in LIKE statement

Question

I am trying to pull data based on multiple keywords from the same column.

Currently I have a SQL statement that works like this.

SELECT * 
FROM Customers
WHERE CustomerName LIKE 'a%'
   OR CustomerName LIKE '_r%'
   OR CustomerName LIKE 'si%';

That works fine. What I am trying to achieve is to pass the keywords c("a", "_r", "si") as a vector. Like this:

keywords <- c("a", "_r", "si")

SELECT * 
FROM Customers
WHERE CustomerName LIKE '%' + keywords + '%';

That did not work. How do I submit a variable with a bunch of keywords into the like statement?

Answer 1

Use sprintf and paste/collapse= . Within a sprintf format %s is replaced with the next argument and %% means %.

keywords <- c("a", "_r", "si")

sql <- keywords |>
  sprintf(fmt = "CustomerName LIKE '%%%s%%'") |>
  paste(collapse = " OR \n") |>
  sprintf(fmt = "SELECT *
    FROM Customers
    WHERE %s")

cat(sql, "\n")

giving:

SELECT *
    FROM Customers
    WHERE CustomerName LIKE '%a%' OR 
CustomerName LIKE '%_r%' OR 
CustomerName LIKE '%si%'