Saturday, October 04, 2025

HTML Stripping function

 Below is the function used to strip the HTML tags from a field in a SQL table. 

ALTER  FUNCTION [dbo].[udf_StripHTML]
	(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT

    SET @Start = CHARINDEX('[if',@HTMLText)
    SET @End = CHARINDEX('if]',@HTMLText, @Start)
    SET @Length = (@End - @Start) + 1
    WHILE ( @Start > 0 	AND @End > 0 AND @Length > 0 )
	BEGIN
		SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
		SET @Start = CHARINDEX('[if',@HTMLText)
		SET @End = CHARINDEX('if]',@HTMLText,@Start)
		SET @Length = (@End - @Start) + 1
	END


    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText, @Start)
    SET @Length = (@End - @Start) + 1
    WHILE ( @Start > 0 	AND @End > 0 AND @Length > 0 )
	BEGIN
		SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
		SET @Start = CHARINDEX('<',@HTMLText)
		SET @End = CHARINDEX('>',@HTMLText,@Start)
		SET @Length = (@End - @Start) + 1
	END


    set @HTMLText = REPLACE(@HTMLText, '&nbsp', ' ');

    while ( PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' +CHAR(13) +']%', @HTMLText COLLATE Latin1_General_100_BIN2 ) > 0)
    begin 
        set @HTMLText = REPLACE(@HTMLText, SUBSTRING(@HTMLText, PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' + CHAR(13) +']%'  , @HTMLText COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
    end 

    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

 

No comments: