Replace the multiple values between 2 characters in azure sql

• I have taken input table named check_1 and sample data is inserted into that table.

• This query removes only the first occurring pattern.

SELECT [Comments],REPLACE([Comments], SUBSTRING([Comments], CHARINDEX('<', [Comments]), CHARINDEX('>', [Comments]) - CHARINDEX('<', [Comments]) + 1),'') AS result
FROM check_1

• In order to remove all string patterns beginning with ‘<‘ and ending with ‘>’ in the text, a user defined function with a while loop is created.

CREATE  FUNCTION [dbo].[udf_removetags] (@input_text VARCHAR(MAX)) RETURNS  VARCHAR(MAX)
AS
BEGIN
DECLARE @pos_1 INT
DECLARE @pos_n INT
DECLARE @Length INT
SET @pos_1 =  CHARINDEX('<',@input_text)
SET @pos_n =  CHARINDEX('>',@input_text,CHARINDEX('<',@input_text))
SET @Length = (@pos_n - @pos_1) +  1

WHILE @pos_1 >  0  AND @pos_n >  0  AND @Length >  0
BEGIN
SET @input_text =  replace(@input_text,substring(@input_text,@pos_1,@Length),'')
SET @pos_1 =  CHARINDEX('<',@input_text)
SET @pos_n =  CHARINDEX('>',@input_text,CHARINDEX('<',@input_text))
SET @Length = (@pos_n - @pos_1) +  1
END

RETURN  @input_text
END

select [dbo].[udf_removetags](comments) as result from check_1

Output String:
The best part is that. Note: reading : moral stories from an early age not only helps your child.in learning important: life lessons but it also helps, in language development.

You can also use Stuff [Refer Microsoft document on STUFF] in place of replace+substring function.
Replace this SET @input_text = replace(@input_text,substring(@input_text,@pos_1,@Length),'')
line with the line
SET @input_text = STUFF(@input_text,@pos_1,@Length,'')
in the user defined function.

Result will be same.