How can I join two tables on a REGEX match in MySQL?
<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>