Replace the multiple values between 2 characters in azure sql-CodePudding

In Azure SQL, I'm attempting to delete any text that is present between the < and > characters to my column in my table

Sample text:

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.< ./span>

Output:

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

I tried below query its working only for small comments text:

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

CodePudding user response：

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

enter image description here

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

enter image description here

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.

CodePudding user response：

According to https://learn.microsoft.com/../azure/../regexp_replace Azure supports REGEXP_REPLACE.

This means it should be possible to replace all '<...>' by '' via

select regexp_replace(comments, '<[^>]*>', '') from mytable;