Renaming all occurances of an HTML class name inside a table with SQL

All we need is an easy explanation of the problem, so here it is.

In a MYSQL database of a CMS all occurances of a specific HTML class name should be pre-fixed.
Let’s say the table of interest is table_article and the HTML code inside this table is like this:

<div>
  <p class="myclass someotherclass1 someotherclass2">Some text.</p>
  <p class="someotherclass1 myclass someotherclass2">Some more text that contains the class name not as class, but as text: myclass</p>
</div>

So the SQL statement should rename the class name "myclass" to "prefix-myclass" wherever it may occur as an HTML class name, but without renaming the occurences of the string when it’s not a class name:

<div>
  <p class="prefix-myclass someotherclass1 someotherclass2">Some text.</p>
  <p class="someotherclass1 prefix-myclass someotherclass2">Some more text that contains the class name not as class, but as text: myclass</p>
</div>

What’s the SQL statement for such an operation?

EDIT: the HTML classes may occur not only in

tags, they can occur in every possible HTML tag.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SELECT html,
       REGEXP_REPLACE(html, '(<p .*class.*"| )myclass(.*[^p]>)', '$1prefix-myclass$2')
FROM test

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e675fc3259589daa5231996b506553a4

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply