Remove HTML Tags from MSSQL Column Data

Recently I have assigned a task to format data in column to display  and remove Html tags and related special characters. It was fun to develop that code. all we need to identify start and end index of htlm tag "< >" to replace with empty string.

Here you go ..

--Create temp table CREATE TABLE #tmp(PrdName VARCHAR(10),HtmlData VARCHAR(2000)
); 

--Loading test values
INSERT INTO #tmpSELECT 'prd1','<b class="newitem"></b> text<sup>&#174;</sup> - prd1' UNION ALLSELECT 'prd2','<b>prd2</b>' UNION ALLSELECT 'prd3','<b>prd3</b>' ;

--================================

--Create Function to remove html tags

--================================  
CREATE  FUNCTION fnClearHTMLTags ( @HTMLString NVARCHAR(MAX) )RETURNS NVARCHAR(MAX)AS
BEGIN
DECLARE     @Start INT ,
            @End INT ,
            @Length INT
    
  WHILE CHARINDEX('<', @HTMLString) > 0 AND CHARINDEX('>', @HTMLString, CHARINDEX('<', @HTMLString)) > 0
   BEGIN
  SELECT  @Start  = CHARINDEX('<', @HTMLString) , --selecting start index of html tag
          @End    = CHARINDEX  ('>', @HTMLString, CHARINDEX('<', @HTMLString)) -- selecting last index of html tag
  SELECT  @Length = ( @End - @Start ) + 1  --selecting length of html tag data to remove
        
      IF  @Length > 0
   BEGIN
   --Stuff function will replace data between <> to empty string ''
   -- replace function is used to replace other special characters e.g. &,# to empty string
  SELECT  @HTMLString = REPLACE(REPLACE(REPLACE(REPLACE(STUFF(@HTMLString,@Start, @Length,''), '&', ''),'#', ''), '; -','.'), '*', '')
              
     END
     END
    
RETURN LTRIM(RTRIM(@HTMLString ))
END  ;
--================================

--Display Data

--================================   
 SELECT prdName,
       HtmlData,
       dbo.fnClearHTMLTags(HtmlData) AS CleanHtmlData
FROM #tmp;




--================================

--Drop Temp Table

--================================  
DROP TABLE #tmp;-- Drop Function
-- DROP FUNCTION fnClearHTMLTags;