• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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
1 Solution
Jim P.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: http://www.ascii.cl/htmlcodes.htm
garethtnashAuthor Commented:
Thank you

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now