title: Join two dataframes on one column that contains substring of other
body: <p>I am trying to left-join <code>df2</code> onto <code>df1</code>.</p>
  <p><code>df1</code> is my dataframe of interest, <code>df2</code> contains additional information I need.</p>
  <p>Example:</p>
  <pre><code>#df of interest onto which the other should be joined
  key1 &lt;- c(&quot;London&quot;, &quot;Paris&quot;, &quot;Berlin&quot;, &quot;Delhi&quot;) 
  other_stuff &lt;- c(&quot;Tea&quot;, &quot;Coffee&quot;, &quot;Beer&quot;, &quot;Tea&quot;) 
  df1 &lt;- data.frame(key1, other_stuff)
  
  #additional info df
  key2 &lt;- c(&quot;London and other cities&quot;, &quot;some other city&quot;, &quot;Eastberlin is history&quot;, &quot;Berlin&quot;, &quot;Delia is a name&quot;, &quot;Delhi is a place&quot;) 
  more_info &lt;- c(&quot;history&quot;, &quot;languages&quot;, &quot;trades&quot;, &quot;art&quot;, &quot;commerce&quot;, &quot;manufacturing&quot;)
  df2 &lt;- data.frame(key2,more_info)
  </code></pre>
  <p>What I now want is that <code>df2$key2</code> is searched for the precise occurrence of <code>df1$key1</code> and then merged onto <code>df1</code> (e.g. match Berlin to Berlin, but not to Eastberlin, and Delhi to Delhi but not to Delia) while ignoring the other words surrounding the match.</p>
  <p>Desired outcome:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>key1</th>
  <th>other_stuff</th>
  <th>more_info</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>London</td>
  <td>Tea</td>
  <td>history</td>
  </tr>
  <tr>
  <td>Paris</td>
  <td>Coffee</td>
  <td>NA</td>
  </tr>
  <tr>
  <td>Berlin</td>
  <td>Beer</td>
  <td>art</td>
  </tr>
  <tr>
  <td>Delhi</td>
  <td>Tea</td>
  <td>manufacturing</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried variations of regex_left_join
  <code>joined&lt;- regex_left_join(df1,df2, by = c(&quot;key1&quot; = &quot;key2&quot;),  ignore_case= F)</code></p>
  <p>and fuzzyjoins
  <code>joined&lt;- df1%&gt;% fuzzy_left_join(df2, by = c(&quot;key1&quot; = &quot;key2&quot;), match_fun = str_detect)</code></p>
  <p>They both only give a result for the exact match (key1=key2=Berlin) and give NA for everything else.</p>
  <p>How do I do this?</p>
  <p>I also tried <a href="https://stackoverflow.com/questions/54637189/merging-two-tables-where-one-column-is-substring-of-the-other-in-r">Merging two tables where one column is substring of the other in R</a> but the logic in the SQL there is the wrong way around. I tried several other Stackexchange approaches, but they are &quot;too fuzzy&quot; for my data.</p>