How to find a string inside a huge string in a MYSQL table?

Question

I have a column in a MYSQL table that accepts TEXT which has HTML tags. Now I want to find all the URLs that have .png/.jpg in the HTML img src attribute.

For example:

Table name: mdl_course_sections; Column name: Summary; Data type of Summary: TEXT

Sample record:

Summary
----------    
<table border="0">
<tbody>
<tr>
<td><img style="display: block; margin-left: auto; margin-right: auto;" src="http://xyz.au/abc.png" height="64" width="64" /></a></td>
<td> Learn about video slideshows.</td>
</tr>
<tr>
<td><img src="http://i1325.photobucket.com/albums/u630/teststudplane/plane-2.jpg" height="95" width="100" /></td>
<td> Explore how video can reinvent education.</td>
</tr>
</table>

See MySQL's manual on [regular expressions](http://dev.mysql.com/doc/refman/5.1/en/regexp.html). — Kermit, Dec 10 '12 at 03:17
@njk, HTML + regex = [insanity](http://stackoverflow.com/a/1732454/168868). Don't. — Charles, Dec 10 '12 at 03:19

score 2 · Accepted Answer · answered Dec 10 '12 at 03:18

You are going to struggle to do this inside SQL itself. Don't try, you'll end up pulling your hair out.

Instead, go through each of the entries, use an HTML parser to extract the src attribute of each img tag, and process from there.

The built-in loadHTML method of the DOMDocument class may help, but it can be rather sensitive to poorly-formed HTML. You could also try something like Simple HTML DOM, which lets you use CSS-like selectors, kind of like how jQuery works.

How to find a string inside a huge string in a MYSQL table?

1 Answers1