Extract text from column in select of MySql query

Question

I have a table named sentEmails where the body column contains the body text of an email.

In the body text, there is a substring like:

some link: <a href="https://somelink@somesite.com/somePage.php?someVar=someVal&sentby=agent">Random link text

Using MySql, I need to extract the url from this column like https://somelink@somesite.com/somePage.php?someVar=someVal&sentby=agent

I was thinking something like the below would work by finding the starting location and returning the next 150 chars, of course it actually just returns the first 150 chars.

SELECT LEFT(body, LOCATE('some link: <a href="', body)+150)  AS link
FROM sentEmails
WHERE sent between date_sub(now(),INTERVAL 1 WEEK) and now()
AND body like '%some link:%'
AND toEmail = 'email@gmail.com'

Additional info:

• the link will always be preceded by the text some link:
• Random link text at the end will change
• I can live with getting a bit more of the text than need if I have to, for example, getting https://somelink@somesite.com/somePage.php">Random link text would be acceptable
• the text shown above is a substring of the full body column which contains much more text
• This isnt something Im going to be doing often. Im researching an issue and I need the links from 40-50 of these rows, Im just hoping to avoid having to pull the link manually from each row.
• I can only use MySQL Query Browser to access this DB if I could connect with php, this would be trivial
• The url in question, can have 6-25 parameters in it
• The url in question will always end with this parameter &sentby=agent

Answer 1

If you had two unique delimiters around the URL, then could just use SUBSTRING() to isolate it. One approach would be to replace the two sides of the URL in the anchor tag with a delimeter:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(
    REPLACE(REPLACE(body, '<a href="', '~'), '&sentby=agent">', '&sentby=agent~'), '~', -2),
    '~', 1)
FROM sentEmails
WHERE sent BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW() AND
    body LIKE '%some link:%' AND
    toEmail = 'email@gmail.com'

I replaced <a href=" and "> with ~. If ~ does not occur anywhere in the body column, and if you only have one HTML tag in the body, then this should work.

If the body column is just a big chunk of HTML, then you should consider using xpath and handling this in your app layer.

Answer 2

if you're just trying to extract the link out, can you do instr() and mid function. something like this

select mid(body,substr(body,'="'),substr(body,'">')-substr(body,'="')) from email...

substr(body,'="') = starting position of the link =" and substr(body,'">') is the end position of the link.

MID function takes (str,pos, len) and len = end position - starting position

Answer 3

Thanks to Tim's help, I was able to get this working with the below query:

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(body, 'some link:  <a href="', -1) , 'sentby=agent">', 1) AS link
FROM sentEmails
where sent between date_sub(now(),INTERVAL 1 WEEK) and now()
AND body like '%some link:%'
AND toEmail = 'email@gmail.com'

Answer 4

Doing this kind of search is not convenient. As the table with emails grows in size, the query will be less and less performant.

If this is a new application you're building, you're better with keeping a separate table with the list of URLs used on each sent email. You'd write the URLs to the DB as you send the emails.

The reasoning of this is that the App will do more searches in the DB than sending emails. Therefore, by doing a little extra work when sending emails, you help a lot in the most-expensive usage of the feature, which is the search.

If you still decide to keep the current approach, you'll want to have an index containing the columns (toEmail, sent) in this order.

Other than that, your approach makes sense and will work. Did you actually try it? Does it work for you?