Search code examples
sqlazureazure-sql-databaseazure-synapse

Replace the multiple values between 2 characters in azure sql

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. < br >Note:< br >< u> reading :< /u> < span style="font-family: calibri,sans-serif; font-size: 11pt;"> moral stories from an early age < b>not only helps your child.< /b>< br>< u>in learning important: < /u>< /span>< span style="font-family: calibri; ">life lessons but it also helps, in language development.< /span>< ./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
Solution
    • 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.