Ufdjrw

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

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/([^"]*)';