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.