CODEWITHSUNDEEP
sqlr
Sebastian Zeki
Sebastian Zeki

Reputation: 6874

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?

Upvotes: 1

Views: 35

Answers (2)

akrun
akrun

Reputation: 887088

We can use glue

library(glue)
mylookup <- function(userdefined){
   as.character(glue('SELECT * FROM mydataTable WHERE (', 
      glue_collapse(glue("ItemNM LIKE '%{userdefined}%'"), sep=" OR "), ')'))

  }

mylookup(userdefined)
#[1] "SELECT * FROM mydataTable WHERE (ItemNM LIKE '%US%' OR ItemNM LIKE '%MRI%')"

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388972

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%)"

Upvotes: 1

Related Questions