SQL Replacing HREF of HTML Structure if href contains substring

Question

In my database, there is this column called blog_content that has html structures of blog posts. In those html structures, there are 2 types of links with the following structure:

<a class="service-content-link" href="https://service-domain/en/articles/how-to-do-smth">Link Title</a>
<a class="service-content-link" href="https://service-domain/en/articles/learn-to-do#h_4980b42263" target="_self">Link Title 2</a>

Note --- these anchor tags aren't the only HTML that's on 1 row. 1 Row could have multiple anchor tags. Like this:

<h3 data-post-processed="true" id="h_e1f9f37659">Overview</h3>
<ol>
    <li>
        <p class="no-margin"><a class="service-content-link" href="https://service-domain/en/articles/how-to-do-smth">Link Title</a></p>
    </li>
    <li>
        <p class="no-margin"><a class="service-content-link" href="https://service-domain/en/articles/learn-to-do#h_4980b42263" target="_self">Link Title 2</a></p>
    </li>
            <li>
        <p class="no-margin"><a class="service-content-link" href="https://service-domain/en/articles/learn-to-do#h_4991b33215" target="_self">Link Title 3</a></p>
    </li>
</ol>

I am trying to replace the href attribute of all tags that include "#h_" in their own href.

For example, the row above should look like this:

<h3 data-post-processed="true" id="h_e1f9f37659">Overview</h3>
<ol>
    <li>
        <p class="no-margin"><a class="service-content-link" href="https://service-domain/en/articles/how-to-do-smth">Link Title</a></p>
    </li>
    <li>
        <p class="no-margin"><a class="service-content-link" href="#h_4980b42263" target="_self">Link Title 2</a></p>
    </li>
            <li>
        <p class="no-margin"><a class="service-content-link" href="#h_4991b33215" target="_self">Link Title 3</a></p>
    </li>
</ol>

I'm not sure how to approach this because I've found 2-3 threads relatively similar to mine.

Is this something that's even achievable in SQL?

I am not clear what you have on the database and what you want to change from what into what. Can you try again, good examples are always useful — RiggsFolly
– RiggsFolly, Commented Jul 11, 2022 at 14:49
So there is a cell on a table that has One <a.... or there are multiple <a... tags in the one table cell. Maybe if you showed us this one cell in the table where this has occured and then we can be sure what we have to start with — RiggsFolly
– RiggsFolly, Commented Jul 11, 2022 at 15:06
But it looks like the snippet of code I've added in my post. Not sure how else I can share the cells with you, given that 1 cell could contain 70-80 lines of HTML on average. — tearswep
– tearswep, Commented Jul 11, 2022 at 15:11
Maybe I explained it wrong, but each cell/row under the column blog_content contains HTML and inside that HTML there can be <a> tags that look like the snippet of code from my post. I am trying to replace the HREF attributes that contain '#h_' with '#h_' and everything that follows this separator. So instead of having hrefs like "service-domain/en/articles/learn-to-do#h_4980b42263" I want to replace it to "#h_4980b42263". — tearswep
– tearswep, Commented Jul 11, 2022 at 15:14

Ajax1234 · Accepted Answer · 2022-07-11 15:52:14Z

1

You can use regexp_replace:

select regexp_replace(blog_content, 'href="[^#"]+#h_(.+)"', 'href="#h_$1"') from blog_posts

See fiddle.

edited Jul 11, 2022 at 15:52

answered Jul 11, 2022 at 15:14

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tearswep Over a year ago

I've tried using your SQL snippet after I've replaced my dummy variables, but it seems it stops after the first iteration. So for example, it replaces the first href, but it doesnt replace the others. Any ideas of why that might be? Here's my fiddle without the dummy variables: dbfiddle.uk/…

Ajax1234 Over a year ago

@tearswep The issue is that the a element class names in your latest fiddle are different from the ones in the OP (service-content-link to intercom-content-link). I updated the regex to be service agnostic dbfiddle.uk/…

Collectives™ on Stack Overflow

SQL Replacing HREF of HTML Structure if href contains substring

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related