Here is the fnHTMLDecode UDF:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE FUNCTION dbo.fnHTMLDecode

(

@String varchar(8000)

)

RETURNS varchar(8000)

BEGIN

DECLARE @HTMLDecodedString varchar(8000)

SELECT @HTMLDecodedString = ISNULL(@String, '')

DECLARE @ix int, @iy int, @pos int

SET @pos = 1 -- start at front of string

SET @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString))) -- search for first occurance of encoding delimiter '&#'

WHILE @ix > 0 -- if one is found

BEGIN

SET @iy = CHARINDEX(';', SUBSTRING(@HTMLDecodedString, @pos + @ix + 1, LEN(@HTMLDecodedString))) -- find the encoding terminator ';'

IF @iy IN (2,3,4) -- iy is one larger than the number to be decoded

BEGIN

-- replace all occurrences of '&#<nnn>;' with the char equivalent

SET @HTMLDecodedString = REPLACE(@HTMLDecodedString, SUBSTRING(@HTMLDecodedString, @pos+@ix-1, @iy+2), CHAR(CAST(SUBSTRING(@HTMLDecodedString, @pos+@ix+1, @iy-1) as int)) )

-- move position to one past first replacement

SET @pos = @pos + @ix

END

ELSE

BEGIN

-- If the encoded number is too large, don't decode it; just leave it be and move position one past the front delimiter

SET @pos = @pos + @ix + 1

END

-- search for next matching encoding delimiter

set @ix = CHARINDEX('&#', SUBSTRING(@HTMLDecodedString, @pos, LEN(@HTMLDecodedString)))

END

RETURN @HTMLDecodedString

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO