1

I have a column of names in my dataframe in R and I am trying to create a text variable of an SQL query out of the names as follows...

select * from *table* where *name* in (*data$name[1,]*, *data$name[2,]*...)

While this can be done this using a for loop and paste function, but I am hoping to find an alternative to using a loop. Any idea on how to go about this ?

989
  • 12,579
  • 5
  • 31
  • 53
Gaurav
  • 1,597
  • 2
  • 14
  • 31
  • Are you using `sqldf` package? – zx8754 Jul 10 '15 at 08:58
  • Using RMySQL package – Gaurav Jul 10 '15 at 09:00
  • 1
    Please show some reproducible example – akrun Jul 10 '15 at 09:00
  • Say I have a the following vector of names with following elements... " aa1", "aa2" "ab3" "aa5" "bb7" I need to create a character variable x with the value as... "select * from table where name in (aa1, aa2, ab3, aa5,bb7)" – Gaurav Jul 10 '15 at 09:09
  • Please check this this [link](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for making reproducible example – akrun Jul 10 '15 at 09:16
  • Ok... If we use the following code to generate a character vector using the built in 'mtcars' dataframe in r... `> x <- row.names(mtcars)`... We will get the following vector... `"Mazda RX4" "Mazda RX4" "Datsun 710" ... ` Now I need to create a vector y as follows... `"select * from table where name in ('Mazda RX4','Mazda RX4','Datsun 710',... )"` – Gaurav Jul 10 '15 at 09:34

1 Answers1

1

This should work:

x <- paste0("select * from table where name in (", paste(names(data), collapse=", "), ")")
ulfelder
  • 5,305
  • 1
  • 22
  • 40