Removing HTML Tags from Text Using SQL Server User-Defined Function

 

Introduction:

In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database.

Creating the Function:

First, let's create the SQL Server UDF named udf_StripHTML. This function takes a VARCHAR(MAX) parameter @HTMLText, which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags.

sql
SET QUOTED_IDENTIFIER ON
GO 
CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT 
DECLARE @End INT 
DECLARE @Length INT 
SET @Start = CHARINDEX('<', @HTMLText) 
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText)) 
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, CHARINDEX('<', @HTMLText)) 
SET @Length = (@End - @Start) + 1 
END 
RETURN LTRIM(RTRIM(@HTMLText)) 
END 
GO

Using the Function:

Now that we have created the udf_StripHTML function, let's see how we can use it to remove HTML tags from a text string.

sql
-- Example usage of udf_StripHTML function 
DECLARE @HTMLText VARCHAR(MAX)
SET @HTMLText = '<p>This is <b>some</b> <i>HTML</i> <u>text</u>.</p>' 
SELECT dbo.udf_StripHTML(@HTMLText) AS PlainText

Conclusion:

In this blog post, we've learned how to create a SQL Server User-Defined Function to remove HTML tags from a text string. This function can be useful in various scenarios where you need to extract plain text from HTML content stored in your database.

Feel free to incorporate this function into your SQL Server environment to simplify text processing tasks involving HTML content.