How can I pull an ID from a varchar field and JOIN another table?

I have a field called 'click_target' that stor开发者_StackOverflow中文版es a string of data similar to this:

http://domain.com/deals/244?utm_source=sendgrid.com&utm_medium=email&utm_campaign=website

Using a MySQL query, is it possible to pull the ID (244) from the string and use it to join another table?

---

You can certainly play games with expressions to pull the ID out of this string but I have a bigger worry - you're burning a dependency on the URL format into a query in the database. That's not really a good idea becuase when (I don't say IF) the URL's change your queries will suddenly fail silently - no errors, just empty (if you're lucky) or nonsensical results.

---

It's an ugly hack, but I believe this line will extract your ID for you in the above url.

REVERSE(LEFT(LOCATE('/', REVERSE(LEFT(click_target, LOCATE('?', click_target)-1)))-1))

Basically, I am getting the text between the first '?' and the last '/'. From there you can join on whatever table you want with that value, though I recommend aliasing it or storing it in a variable so that it is not recalculated frequently.

---

If you need the id, fix your database to store it porperly in a separate field.