Update: Replace content with inner content in sql

Question

i have an html element in my database and i would like to replace it with its inner content.

Currently the content looks like this:

<h2><a rel="nofollow noopener" target="_blank" class="chartbeat-section" name="title">title</a></h2>

And i would like to get rid of the a-tags, so that the outcome would look like this:

<h2>title</h2>

Is that somehow possible in sql?

Answer 1

I was able to do it this way:

set @html = '<h2><a rel="nofollow noopener" target="_blank" class="chartbeat-section" name="title">title</a></h2>';

select regexp_replace(@html, '<a [^>]*>([^<]*)</a>', '\\1') AS _result;

Output:

<h2>title</h2>

Tested on MariaDB 10.5: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=6bc5fa7e887729d12231415910860c46

Read about the REGEXP_REPLACE() function in MariaDB: https://mariadb.com/kb/en/regexp_replace/

Please don't ask "now what if it's like ...". I have demonstrated the technique. It's up to you to turn that into knowledge and apply it to other cases.