0

Imagine you have table called Information which could have two columns id and text_with_hrefs_with_or_without_target_blank, which we want to update

So for example one of many rows could have stored this value in the column text_with_href....

first href with target blank before

<a target="_blank" href="http://link1.com">Link1</a>

maybe another text and link with target

<a href="http://www.link2.com">Link2</a>

and last href link with target blank after href

<a href="http://www.link3.com" target="_blank">Link3</a>

I would like to update all similiar rows in table Information to add target=_blank to hrefs in those parts of rows which havent one

so the result for this row I mentioned would look like

first href with target blank before

<a target="_blank" href="http://link1.com">Link1</a>

maybe another text and link with target

<a href="http://www.link2.com" target="_blank">Link2</a>

and last href link with target blank after href

<a href="http://www.link3.com" target="_blank">Link3</a>
Cœur
  • 37,241
  • 25
  • 195
  • 267
Martin Závodný
  • 25
  • 3
  • 3
    Better handled in the presentation layer – Clodoaldo Neto Apr 24 '17 at 16:09
  • Although not really recommended, you could use a regex to do this, I'm just no regex expert. Wasn't able to get this coverted to PG syntax, but I bet it'd work if you could: http://stackoverflow.com/a/6594276/2283954 – Joe Love Apr 24 '17 at 16:25
  • http://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags – pozs Apr 24 '17 at 16:52

1 Answers1

1

As pointed out in comments you better off doing it in your client/presentation code.

Now if you absolutely need to do this in the database, especially if it's just a one time thing, and assuming that you have one and only one link <a></a> per row you can do something as simple as this

UPDATE information
   SET text_with_hrefs_with_or_without_target_blank = 
           replace(text_with_hrefs_with_or_without_target_blank, '<a ', '<a target="_blank" ')
 WHERE text_with_hrefs_with_or_without_target_blank NOT LIKE '%target="_blank"%'

SQLFiddle

peterm
  • 91,357
  • 15
  • 148
  • 157