SQL statement with multiple keywords in string in LIKE statement-CodePudding

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?

CodePudding user response：

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%'

CodePudding user response：

Just another option using string_split() and a JOIN

Example

DECLARE @Find VARCHAR(max) = ('a%,_r%,si%')

Select Distinct A.* 
 From Customers A
 Join string_split(@Find,',') B
   on CustomerName like B.value