Today I came across a problem where a field in a SQL server 2005 database contained encoded HTML (for example, it looked like "<div>Blah blah"). However, as this field was required in a SQL server report, this HTML needed to either be removed or formatted. Since SQL reports don't have a built-in function for this, and trying to create a function for it using their Excel-style expressions didn't sound like much fun, the only option left was to trim it in the SQL code.
Normally, I'd turn to a regular expression to handle this, however in SQL server, to use regular expressions you need to mess about with COM to get a VBScript interface etc, and I'm lazy, so I wanted a simpler way. A bit of research turned up an article at http://blog.sqlauthority.com/ which appeared to do exactly what I needed using just a normal user-defined function, and as luck would have it, it had only been written 11 days ago, so my timing was pretty good ;) Of course, it wasn't exactly what I needed, but it was pretty close, and after a few changes (mostly copy-paste code to handle each HTML entity that was likely to appear), I had a lovely new function which did exactly what I needed it to do. The performance of it is probably pretty dire, but that's acceptable in this case.
Here's the code, should anyone need it:
Of course, most of the credit for this goes to Pinal Dave of SQLAuthority.com, I just hacked his code around a bit to suit my particular needs.
Note the formatting is going a bit awry, mostly due to HTML entities in the code. I'll fix this when I get a chance