R- SQLDF - SELECT ... CASE... END

Question

Hello, I am trying to run a query that use a CASE statement from R. I am using the SQLDF package. The same query works fine in Mysql. The query gives the values of 0 or 1 to "express" according to the following situations:

Select "express" =
CASE
when E_MAIL like "%gmail%" then 1
when E_MAIL like "%yahoo%" then 1
when E_MAIL like "%hotmail%" then 1
else 0
END from data
;

This is what I have tried in R:
alpha<-sqldf( "Select "express"=
CASE
when E_MAIL like "%gmail%" then 1
when E_MAIL like "%yahoo%" then 1
when E_MAIL like "%hotmail%" then 1
else 0
END from data");

Any help will be appreciated!
Thank you

G. Grothendieck · Accepted Answer · 2015-03-13T01:46:19.250

The problems are that the query string in the question has quotes within quotes and the syntax is wrong. Using the default SQLite database (sqldf also supports MySQL) we have:

library(sqldf)
data <- data.frame(E_MAIL = c("x@x.com", "x@yahoo.com"))

sqldf("select E_MAIL,
  case
    when E_MAIL like '%gmail%' then 1
    when E_MAIL like '%yahoo%' then 1
    when E_MAIL like '%hotmail%' then 1
    else 0
  end express
  from data")

giving:

       E_MAIL express
1     x@x.com       0
2 x@yahoo.com       1

Or, perhaps you meant to perform an udpate. This gives the same output with the test data shown:

data <- data.frame(E_MAIL = c("x@x.com", "x@yahoo.com"), express = 0)

sqldf(c("update data set express = 
  case
    when E_MAIL like '%gmail%' then 1
    when E_MAIL like '%yahoo%' then 1
    when E_MAIL like '%hotmail%' then 1
    else 0
  end", "select * from main.data"))

Note: Next time please provide a complete reproducible example including input.

@G.Grothendieck , how do you handle if the criteria on the when is a set of values and you can not use a like .. i.e. WHEN column in and the set is like this c('China','Hong','Taiwan') — E B, Sep 09 '17 at 04:30
If these are exact matches you can create a new data frame from the keywords and join it to the larger data frame or if there are only a few keywords use the sql `in` construct. — G. Grothendieck, Sep 09 '17 at 11:35

R- SQLDF - SELECT ... CASE... END

1 Answers1