question

ajayrawat-5337 avatar image
0 Votes"
ajayrawat-5337 asked EchoLiu-msft edited

replace a data in table

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


sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Hi YitzhakKhabinsky-0887,

Thanks for the reply. Yes, you are right but for the past two days, I was trying to post my question but not able to do so because I was using HTML Scripts in it. So to check if it's not the server problem I post it with plain text.
I have changed my question now and posted it.


Thanks

0 Votes 0 ·

@ajayrawat-5337,

What's the latest?
Did you have a chance to try the proposed solution in the UPDATE section for the specific attributes?

Congratulations. You became famous. Your post became an inspiration for a blog post.
Check it out here: how-to-remove-all-attributes-from-xhtml-tags-in-sql-server

0 Votes 0 ·

Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @ajayrawat-5337,

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.


89480-xml-delete-all-attributes-2021-04-20-131735.png

UPDATE

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

89664-screenshot-2021-04-20-144645.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ajayrawat-5337 avatar image
0 Votes"
ajayrawat-5337 answered YitzhakKhabinsky-0887 edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ajayrawat-5337,

Can we do it using REGEX or replace

No, SQL Server doesn't support REGEX.
You need to use XQuery to do what you are after.

If you need to delete just specific attributes, not all of them, you just need to specify a specific XPath expression to delete a specific attribute. One by one.

Please see the UPDATE section in my answer.

P.S. Please contact me on LinkedIn

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ajayrawat-5337,

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.