Strip HTML Tags from a String

DROP FUNCTION [dbo].[udf_StripHTML]

GO

CREATE FUNCTION [dbo].[udf_StripHTML]

(

@HTMLText VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE

@Start INT,

@End INT,

@Length INT

-- get the start and end points in the string of the first tag

SET @Start = CHARINDEX('<',@HTMLText)

SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

-- process all tag in the string in a loop

WHILE ((@Start > 0) AND (@End > 0) AND (@Length > 0))

BEGIN

-- replace all occurrences of the tag with an empty string

-- this is more efficient than just iterating through the string one tag at a time

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')

-- get the next tag location

SET @Start = CHARINDEX('<',@HTMLText)

SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

END

-- return the stripped string

RETURN LTRIM(RTRIM(@HTMLText))

END

GO

DECLARE @InputString VARCHAR(MAX),

@OutputString VARCHAR(MAX)

-- an HTML extract from a code section one of the web pages in this site

SELECT @InputString = '</SPAN><SPAN style="COLOR: blue">END<BR>

<BR>

<BR>

</SPAN><SPAN style="COLOR: green">/*<BR>

select [dbo].[udf_padstring] (''Hello World'',''x'',1,0)<BR>

select [dbo].[udf_padstring] (''Hello World'',''x'',1,1)<BR>

select [dbo].[udf_padstring] (''Hello World'',''x'',1,2)<BR>

select [dbo].[udf_padstring] (''Hello World'',''x'',1,3)<BR>

<BR>

*/<BR>

</SPAN></CODE></P>'

SELECT @OutputString = [dbo].[udf_StripHTML](@InputString)

SELECT @OutputString