MS SQL Trigger - Remove HTML Attributes on Insert

Hello Experts,

I would be grateful for a little help with an MS SQL 2008 trigger that runs when an Insert / Update Stored Procedure is called. The trigger needs to do the following -

Firstly (UnEncode data that has already been encoded / double encoded )

'Clean all instances of &amp
-- Replace & with &
'Clean all instances of &
-- Replace & with &
Clean all instances of £
-- Replace £ with £
Clean all instances of &quote;
-- Replace &quote; with "
Clean all instances of '
-- Replace ' with '

Having done this Encode the clean unencoded variable (starting with &, to ensure that there is no instance of double encoding (&pound)) --

Replace & with &
Replace £ with £
Replace " with &quote;
Replace ' with &apos'

Thirdly, It is highly likely that there will be HTML Elements <p><b><ul> etc within the data that needs cleaning, and highly likely therefore that these elements will contain attributes (Class, is, style, title). I'm happy to keep the elements, but I need to remove any attributes, so that <p style="color:red;" id="maintext" class="redtext" title="a paragraph">xxxx</p> becomes <p>xxxx</p>

Finally again it is likely that the data has been pasted from word, so I need to remove thinks like tabs, and replace line breaks with <br /> as well as remove any <a href>xxx</a> links

I haven't got a clue where to start with this? Appreciate any help you can offer.

Thank you
Who is Participating?
Jim P.Connect With a Mentor Commented:
All I can say is AAAGGGHHHH!!!!

Really the best bet is write to a staging table. Then have a stored procedure that does a search and replace for the HTML characters.

This is the table to look at:
garethtnashAuthor Commented:
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.