title: Use both LIKE and NOT LIKE in the same query
body: <p>I have a <code>df</code> like this </p>
  
  <pre><code>Category &lt;- c('D_L','D_R','FA1','LBP0W','L-010','L-020','LW_-010','LWA_PT_035','LWA_PT_055','RBP0W','RET_MAG','R-010','R-000','RWA_PT_035','RWA_PT_055','TPH')
  ID &lt;- c(111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126)
  df &lt;- data.frame(ID,Category)
  df
  
      ID   Category
  1  111        D_L
  2  112        D_R
  3  113        FA1
  4  114      LBP0W
  5  115      L-010
  6  116      L-020
  7  117    LW_-010
  8  118 LWA_PT_035
  9  119 LWA_PT_055
  10 120      RBP0W
  11 121    RET_MAG
  12 122      R-010
  13 123      R-000
  14 124 RWA_PT_035
  15 125 RWA_PT_055
  16 126        TPH
  </code></pre>
  
  <p>I am using sqldf to filter my dataset into 2 categories. </p>
  
  <pre><code>df_R &lt;- sqldf("SELECT * FROM df 
                    WHERE Category NOT LIKE '%_L'
                    AND Category NOT LIKE 'LW_%'
                    AND category NOT LIKE 'L-%'
                    AND category NOT LIKE 'LB%'")
  
  df_L &lt;- sqldf("SELECT * FROM df 
                WHERE Category NOT LIKE '%_R'
                AND Category NOT LIKE 'RW_%'
                AND category NOT LIKE 'R-%'
                AND category NOT LIKE 'RB%'")
  </code></pre>
  
  <p>Here I get 2 data frames.
  Challenge is that: </p>
  
  <p>1) for df_R - I need to return "RWA_PT_035" &amp; not "RWA_PT_055" category 
  2) for df_L - I need to return "LWA_PT_035" &amp; not "LWA_PT_055" category </p>
  
  <p>and hence when I try to do this </p>
  
  <pre><code>df_R &lt;- sqldf("SELECT * FROM df 
                        WHERE Category NOT LIKE '%_L'
                        AND Category NOT LIKE 'LW_%'
                        AND Category NOT LIKE 'L-%'
                        AND Category NOT LIKE 'LB%'
                        AND Category LIKE 'RWA_PT_035'")
  </code></pre>
  
  <p>it returns only 1 observation and that is "RWA_PT_035" for df_R but my desired output is </p>
  
  <pre><code>   ID   Category
  1 112        D_R
  2 113        FA1
  3 120      RBP0W
  4 121    RET_MAG
  5 122      R-010
  6 123      R-000
  7 124 RWA_PT_035
  8 126        TPH
  </code></pre>
  
  <p>and for df_L </p>
  
  <pre><code>    ID   Category
  1  111        D_L
  2  113        FA1
  3  114      LBP0W
  4  115      L-010
  5  116      L-020
  6  117    LW_-010
  7  118 LWA_PT_035
  8  121    RET_MAG
  9 126        TPH
  </code></pre>
  
  <p>I would like to know if I could use "LIKE" and "NOT LIKE" in a query at the same time like the above? or if there is any other way to do this? </p>
  
  <p>I am also open to other methods like data.table or dplyr instead of sqldf. </p>