CODEWITHSUNDEEP
postgresqlpattern-matching
VBMali
VBMali

Reputation: 1380

How to replace particular string in table column value in PostgreSQL

I am trying to replace certain text with other text in PostgreSQL.

To be more specific, I am trying to replace image path and anchor href in article (table blog_posts) from relative to absolute path. Some of the images and anchors already have an absolute path that should not get disturbed.

I tried to select the records which I need to modify:

SELECT
  bp.id,
  bp.article,
FROM
  blog_posts bp
WHERE
  bp.article LIKE '%src=%"/fixed_word/%' 
  OR  
  bp.article LIKE '%src="/fixed_word/%'
 OR
 bp.article LIKE '%href="/fixed_word/%'
 OR
 bp.article LIKE '%href=%"/fixed_word/%'

now I am not sure how to proceed further to update. Please help to get right solution.

My data is something like this:

MyTable: blog_posts

id article
1  any text <img any-atribute src="/fixed_word/variable_word1/something.png"/> any text
2  any text <a any-attribute href="/fixed_word/variable_word2/something2.png"><img src="/fixed_word/variable_word2/something2.png"/> </a>any text
3  any text <img src="https://mydomain.subdomain.com/fixed_word/variable_word1/something.png"/> any text
4  any text <img any-attribute src=\"/fixed_word/variable_word1/something.png"/> any text
5  any text <a any-attribute href=\"/fixed_word/variable_word2/something2.png"><img src=\"/fixed_word/variable_word2/something2.png"/> </a>any text
6  any text <img any-attribute src="https://mydomain.subdomain.com/fixed_word/variable_word6/something6.png"/> any text

OutPut should be:

id article
1  any text <img any-atribute src="https://mydomain.subdomain.com/fixed_word/variable_word1/something.png"/> any text
2  any text <a any-attribute href="https://mydomain.subdomain.com/fixed_word/variable_word2/something2.png"><img src="https://mydomain.subdomain.com/fixed_word/variable_word2/something2.png"/> </a>any text
3  any text <img src="https://mydomain.subdomain.com/fixed_word/variable_word1/something.png"/> any text
4  any text <img any-attribute src="https://mydomain.subdomain.com/fixed_wordvariable_word1/something.png"/> any text
5  any text <a any-attribute href="https://mydomain.subdomain.com/fixed_word/variable_word2/something2.png">
6  any text <img any-attribute src="https://mydomain.subdomain.com/fixed_word/variable_word6/something6.png"/> any text

Upvotes: 1

Views: 152

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

This can be a starting point:

UPDATE blog_posts
SET article = regexp_replace(
                 article,
                 E'(src|href)=[^"]*"/fixed_word/([^"]*)',
                 E'\\1="https://mydomain.subdomain.com/fixed_word/\\2',
                 'g'
              )
WHERE article ~ E'(src|href)=[^"]*"/fixed_word/([^"]*)';

Upvotes: 2

Related Questions