Postgresql - clear mid-line HTML tags

Question

If I have this in the Postgresql 9.1 column:

foo foo <th id="ddd"> foo foo <th id="www"> foo 

And I want it to update:

 foo foo <th> foo foo <th> foo 

I tried regex_replace but I failed.

Answer 1

Assuming you have a table like this:

 CREATE TABLE table1 ( a character varying NOT NULL, ... ) 

You can use the following regexp_replace:

 update table1 set a = regexp_replace(a, '(.*?)<(\w+)\s+.*?>(.*)', '\1<\2>\3', 'g'); 

The 'g' flag indicates the replacement of all matching patterns, not just the first.

With this input:

 foo foo <th id="ddd"> foo foo <th id="www"> foo<div id="hey"> 

I get the following output:

 foo foo <th> foo foo <th> foo<div>