title: How can I join two tables on a REGEX match in MySQL?
body: <p>I have a table called <code>flights</code> where the <code>reservationNum</code> column is the confirmation number for each flight booking (an 8 digit number)</p>
  
  <p>I have a table named <code>sentEmails</code> where the <code>body</code> column is the body of the email that was sent. The 8 digit confirmation number of the booking the email is for is in the text stored in the <code>body</code> column.</p>
  
  <p>I need to pull all rows from <code>sentEmails</code> where the subject contains <code>Some text</code> but I also need some other info that is in the <code>flights</code> table row associated with that booking</p>
  
  <p>I thought I might be able to join the tables on a regex match like the below:</p>
  
  <pre><code>Select *
  from sentEmails se 
  join flights f on f.reservationNum = se.body REGEXP '\d{8}'
  where subject like '%Some text%'
  and sentDate &gt; '2016-02-01'
  </code></pre>
  
  <p>For the first row this <em>seems</em> to work giving info from both tables. Unfortunately, the data from <code>flights</code> is not correct and after that first row all of the remaining rows have the same <code>sentEmails</code> data and no data for the <code>flights</code> columns</p>
  
  <p><strong>Is it possible to join two tables on a REGEX match in MySQL? And If not, how can I retrieve the desired data?</strong></p>
  
  <p><strong>Note</strong> Given the standardized content in the emails I can be 100% sure that the only 8 digit number each contains is the one I need</p>