I have data in the my tables which can contain text like the below.

As you can see it can contain HTML Anchors. I need to remove this html, and replace it with a link in the format of {url:http://www.venuesevent.com/qa/,click here}

Issue your certificate to you electronically.
To find out more <a shape="rect" href="http://www.qa.com" alt="tt" name="fff">click here</a> blah blah
As you can see it looks like I need to attain the url attribute and the inner text, create the link and replace what is currently in place.

I need to do this in TSQL, however the only information I can find on the net is for removing HTML entirely.

NB: The text in the columns other than the links needs to remain and the data could contain more than one link with various attributes.