create user defined WHERE SQL phrase in r

I am using RODBC to connect to a database. I would love for a user to be able to define wildcard strings to lookup in the SQL as part of a function. I cannot use CONTAINS as the database is not full-text indexed.

The SQL I want to create is

"SELECT *
FROM mydataTable
WHERE (ItemNM LIKE '%CT%' OR ItemNM LIKE '%MRI%' OR ItemNM LIKE '%US%')"

The user should be able to define as many wildcards as they like, all from the ItemNM field and all separated by OR.

myLookup<-function(userdefined){

  paste0("SELECT *
FROM mydataTable
WHERE ( LIKE '",userdefined,"')")


}

If I vectorise the userdefined (ie userdefined<-c("US","MRI")) then I end up with separate SQL strings which is no good. How can I get the output as above but for any length of user defined string where they are just defining the wildcard?

Answer

You could use :

myLookup <- function(userdefined) {
   paste0('SELECT * FROM mydataTable WHERE (', 
       paste0('ITENM LIKE %', userdefined, '%', collapse = " OR "), ')')
}

userdefined<-c("US","MRI")
myLookup(userdefined)
#[1] "SELECT * FROM mydataTable WHERE (ITENM LIKE %US% OR ITENM LIKE %MRI%)"