[RESOLVED] SQL Server replace HTML anchors in Text Col with different style link

[FishGuy]

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.

[FishGuy]

-- lose the dreaded <br> HTML markup in the text!
update @prereqs set message = replace(message, '<br>', @newline)
update @prereqs set message = replace(message, 'shape="rect"', '')
update @prereqs set message = replace(message, 'target="_blank"', '')
update @prereqs set message =STUFF(message,
CHARINDEX('text="', message),
CHARINDEX('"', message, CHARINDEX('"', message) + 6) - CHARINDEX('text="', message) + 6,
''
)
update @prereqs set message =STUFF(message,
CHARINDEX('name="', message),
CHARINDEX('"', message, CHARINDEX('"', message) + 6) - CHARINDEX('name="', message) + 6,
''
)
update @prereqs set message =STUFF(message,
CHARINDEX('id="', message),
CHARINDEX('"', message, CHARINDEX('"', message) + 4) - CHARINDEX('id="', message) + 4,
''
)

update @prereqs set message = replace('href="',message,'')
update @prereqs set message = replace('<a',message,'{url:')
update @prereqs set message = replace(message, '">', ',')
update @prereqs set message = replace(message, '"</a>', '}')