SQL

convert html to plain text in sql server

-- select [dbo].[fn_ConvertToTextByHtml] ('<p>I love my country</p>')

CREATE FUNCTION [dbo].[fn_ConvertToTextByHtml] (@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