Friday, May 2, 2014

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;