replace a data in table

Question

Hi Team,

I have one table that contains HTML data in it.
I want to replace data in one go but not getting how to do it.
I have to remove data from the previous string and make it a new string example is given below .

Previous:
<p><span style="font-size: 18px;"><strong>Hello World</strong></span></p>
<p> </p>
<h2 style="font-size: 24px; text-align: center;">123456</h2>
<p> </p>
<p class="next-steps-intro" style="margin-bottom: 0;"><span style="font-size: 18px;"><strong>You are at home:</strong></span></p>

---

NEW:

<p><span><strong>Hello World</strong></span></p>
<p> </p>
<h2 >123456</h2>
<p> </p>
<p><span><strong>You are at home:</strong></span></p>
<p> </p>

---

Here I have to remove data like
<span style="font-size: 18px;">
now it should be
<span>

same i have to do it with
<h2 style="font-size: 24px; text-align: center;">
now it should be
<h2>

same i have to do it with
<p class="next-steps-intro" style="margin-bottom: 0;">
now it should be
<p>
Like this, I have to replace.
Can I do it using Regex or replace any suggestion.

Thanks

Answer 1

Hi @ajay rawat ,

My understanding is that you need to remove all attributes from (X)HTML tags.

Please try the following solution.
I am really sorry for a picture. Alas, this forum doesn't allow to have HTML in the source code.

UPDATE

for <span style="font-size: 18px;">

Answer 2

Hi YitzhakKhabinsky-0887,

Thanks for the reply. Here I don't want to remove all attributes from (X)HTML tags
I am rephrasing my question this what exactly I want.

I have one table in that we have a column name link that contains HTML values. I want to replace the value.

Note : below data is present in a single string as you can see above. Here I am showing what I have to replace.

Here I have to remove data like Previously it was like

<span style="font-size: 18px;">
this thing i have to delete " style="font-size: 18px;" now it should look like

<span>

same i have to do it with
<h2 style="font-size: 24px; text-align: center;">
this thing i have to delete " style="font-size: 24px; text-align: center;" now it should look like

<h2>

same i have to do it with
<p class="next-steps-intro" style="margin-bottom: 0;">
this thing i have to delete " class="next-steps-intro" style="margin-bottom: 0;" now it should look like
now it should look like
<p>

---

Can we do it using REGEX or replace

Thanks

Answer 3

Try this. You may need to convert the code to a function for your case:

DECLARE @s varchar(1000);
SET @s = 'HTML String'  -- Replace Your HTML String here
DECLARE @s1 varchar(1000);
DECLARE @s2 varchar(1000);
DECLARE @rowId int = 1;
DECLARE @maxRowId int;
DECLARE @tagName varchar(20);
DECLARE @tagNameToBeSearched varchar(20)
DECLARE @TagNames TABLE (
    RowId int IDENTITY(1, 1) NOT NULL,
    TagName varchar(20)
);
INSERT INTO @TagNames VALUES
-- Add tags here
('span'), ('h2'), ('p');

SELECT @maxRowId = MAX(RowId) FROM @TagNames;

WHILE @rowId <= @maxRowId
BEGIN
    SELECT @tagName = TagName FROM @TagNames WHERE RowId = @rowId;
    SET @tagNameToBeSearched = '<' + @tagName + ' ';
    WHILE CHARINDEX(@tagNameToBeSearched, @s) > 0
    BEGIN
        SET @s1 = LEFT(@s, CHARINDEX(@tagNameToBeSearched, @s) + LEN(@tagNameToBeSearched) - 2);
        SET @s2 = SUBSTRING(@s, CHARINDEX(@tagNameToBeSearched, @s) + LEN(@tagNameToBeSearched) - 1, LEN(@s) - 1);

        WHILE LEFT(@s2, 1) <> '>'
        BEGIN
            SET @s2 = SUBSTRING(@s2, 2, LEN(@s2));
        END

        SET @s = @s1 + @s2;
    END

    SET @rowId = @rowId + 1;
END

PRINT @s;

Answer 4

Hi @ajay rawat ,

When you have a large amount of data, Guoxiong’s method does not seem to be easy.I found a function that uses regular expression fuzzy substitution,but its result is slightly different from the output you expect：

    --If it exists, delete the original function  
    IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL    
        DROP FUNCTION dbo.RegexReplace   
    GO   
      
    CREATE FUNCTION dbo.RegexReplace   
    (   
        @string VARCHAR(MAX),     
        @pattern VARCHAR(255),   
        @replacestr VARCHAR(255),   
        @IgnoreCase INT = 0   
    )   
    RETURNS VARCHAR(8000)   
    AS    
    BEGIN   
        DECLARE @objRegex INT, @retstr VARCHAR(8000)   
        EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT   
        EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern   
        EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase   
        EXEC sp_OASetProperty @objRegex, 'Global', 1   
        EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr   
        EXECUTE sp_OADestroy @objRegex   
        RETURN @retstr   
    END   
    GO   
      
    --To ensure normal operation, you need to set the Ole Automation Procedures option to 1  
    EXEC sp_configure 'show advanced options', 1     
    RECONFIGURE WITH OVERRIDE    
    EXEC sp_configure 'Ole Automation Procedures', 1     
    RECONFIGURE WITH OVERRIDE  
      
    SELECT dbo.RegexReplace(yourcol,'\=[^<>]*\;"','',1) FROM yourtable  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.