How to replace particular string in table column value in PostgreSQL

Question

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

Laurenz Albe · Accepted Answer · 2019-03-12 06:46:12Z

2

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

edited Mar 12, 2019 at 6:46

answered Mar 8, 2019 at 10:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

16 Comments

VBMali Over a year ago

Hi Laurenz, I again checked the possible bad data for the ` \ ` used in src or href path. fortunately there is not such data. so there is no worry about '\' there will be always ' / ' in src or href. but there might be that starting '/' in src=/"\fixed_word... Accordingly I need the help, I will be changing the question details for the same. Sorry for inconvenience!

VBMali Over a year ago

Error: parentheses () not balanced

Laurenz Albe Over a year ago

My statement works for your example, there are no unbalanced parentheses. Could it be that you need to escape certain special characters in string constants in the programming language you are using?

Laurenz Albe Over a year ago

If you have standard_conforming_strings = off, you need to double all backslashes. I'll edit the answer accordingly. But you should really use standard_conforming_strings = on.

Laurenz Albe Over a year ago

I have removed all the backslashes since you don't need them. This simplifies the code. You should test it thoroughly before using it, since I won't guarantee correctness.

|

Collectives™ on Stack Overflow

How to replace particular string in table column value in PostgreSQL

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related