Call for SQL experts

So, I have an sql guru at my office that I spoke to a bit this morning. He thinks it's doable but not all in native sql. I'm still thinking this process through. Bear with me, My thinking is it will need to happen external to the db with a wrapper script, (I've done this in Perl), likely on a per record iteration.

- find each post with a pb link(easy) and parse the image link(easy)
- download the image to local gallery (where failure may occur as pb is slow or throttling, broken links, etc.) and store the new reference (not hard)

the updates to the production table are where I'm still thinking through it. Inserting the new url link into the old body text is the trick I think, especially posts that have multiple pb links...

sql doesn't have much in the way of regex natively, that's why I think Perl. Maybe parse the body field into an array, splitting in IMG tags...

I should have most of the conversion done later today or tomorrow.

Tim said:

If we can get an output table of Member ID number and the photo link URL (one row per link) and a 3rd column for the edited link, then I can update the target link column to whatever it needs to be.

I might even get each member to update in an Excel file or something their own links based on where they upload the pics to / change filenames to etc. Then do the update to the DB table member by member.

Click to expand...

I'll work on creating the select,download, new url routine.

And it's reminding me that I'm rusty.