Keith Rull

This is an interesting piece of sql, the function accepts a string and removes HTML elements that is inside the string. This is genereally useful when you have data that has embedded HTML content on it. The function was written by Robert Davis.

/******
Object: User Defined Function dbo.fnStripTags
Script Author: Robert Davis, robertd@realtechllc.com
Purpose: Strip HTML tags out of text. Anything enclosed in '<' and '>' will be removed.

sample usage:
Declare @MyText varchar(30)
Set @MyText = '<b>My <i>sample</i> Text</b>'

Select dbo.fnStripTags(@MyText)

this returns: My sample Text

******/

Create Function dbo.fnStripTags
    (@Dirty varchar(4000))
    Returns varchar(4000)
As

Begin
    Declare @Start int,
        @End int,
        @Length int

    While CharIndex('<', @Dirty) > 0 And CharIndex('>', @Dirty, CharIndex('<', @Dirty)) > 0
     Begin
        Select @Start = CharIndex('<', @Dirty),
         @End = CharIndex('>', @Dirty, CharIndex('<', @Dirty))
        Select @Length = (@End - @Start) + 1
        If @Length > 0
         Begin
            Select @Dirty = Stuff(@Dirty, @Start, @Length, '')
         End
     End

    return @Dirty
End