Replace HTML Tags from SQL Text Output

We may come across some text fields in SQL table having values like '..xyz<p>asdff...', in that case we need to replace HTML tags.

To achieve that create below function and use them in SQL procedures:

CREATE FUNCTION [dbo].[RemoveHTMLTag] ( @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 REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@HTMLText)), '$', '. '), '£', '. '), '€', '. ')
   END

In Procedure:

SELECT dbo.RemoveHTMLTag(ColumnName) FROM Table